コミュニティ

• 

  Permanently deleted user

  • 2018年09月21日 07:34

  Hello,

  Could you please describe your purpose and scenario in more details? If you want to check the recognized value from image you may create the database check rule.

  いいえ 0 はい
• 

  Claus Nielsen

  • 2018年10月03日 07:56

  Hi again,

  When processing invoices from a vendor they might send the same invoice both on e-mail and physical paper. For example the e-mail could arrives today and the paper in 2 days.

  I know in the standard invoice projekt there is no validation, but I was wondering if it's possible to make the following validation:

  The invoice number (invoice from the e-mail) should be validated agains the ERP system (using the vendor number as a parameter). If NO result is found (meaning the invoice has not been registered before), it should just continiue.

  2 days later the same invoice arrives on paper and someone scans it.

  Now the invoice has allready been registered in the ERP system and FC should stop with an error telling that the invoice number has allready been registered and the user should the delete in invoice.

  いいえ 0 はい
• 

  Permanently deleted user

  • 2018年10月04日 02:29

  Hi Diskboy,   Please see rule script I am using below and let me know if you have any questions.   This uses Invoice Number, SupplierNumber(Vendor Number),and Invoice amount to check for duplicates in a table. If a duplicate is found it then updates called duplicatefound with "duplicate". If a no duplicate is found it updates the fields with "No Duplicate" You can then make set the allowed values to "No Duplicate" for the duplicate found field so that it will stop at verification if it is any other value.   You first need to create an ODBC environment variable to use in the connection string.      

  using System;

  using System.Data;

  using System.Data.SqlClient;

  using System.Collections.Generic;

   

  string invNumber = Context.Field("InvoiceNumber").Text;

  string invTotal = Context.Field("Total").Text;

  decimal intTotal = 0;

  string invSupplier = Context.Field("SupplierNumber").Text;

   bool isNumeric = decimal.TryParse(invTotal, out intTotal);

  int invCount = 0;

   string dbConn = FCTools.ScriptContext.Project.EnvironmentVariables.Get("DBVariable).Replace("Provider=SQLOLEDB.1;","").Replace(";Provider=SQLOLEDB.1;","");

  if(invNumber != "" && invTotal != "" && invSupplier != "" && isNumeric == true)

  {

      try

      {

          using (SqlConnection oSqlConnection = new SqlConnection(dbConn))

          {

              oSqlConnection.Open();

              string strSql = "SELECT COUNT(INVOICE_NUM) AS InvCount FROM hsi.v_MTS_Oracle3MonthInvoice WHERE INVOICE_NUM = @InvNum AND VENDOR_ID = @SupNum  AND INVOICE_AMOUNT = @InvTotal group by invoice_num, VENDOR_ID, INVOICE_AMOUNT";

              using (SqlCommand oSqlCommand = new SqlCommand(strSql, oSqlConnection))

              {

                  oSqlCommand.Parameters.AddWithValue("@InvNum", invNumber);

                  oSqlCommand.Parameters.AddWithValue("@InvTotal", invTotal);

                  oSqlCommand.Parameters.AddWithValue("@SupNum", invSupplier);

                  int count = Convert.ToInt32(oSqlCommand.ExecuteScalar());

                  if (count > 0) //Part Found

                  {

                      Context.Field("DuplicateFound").Text = "Duplicate";

                      return;

                  }

                  else

                  {

                      Context.Field("DuplicateFound").Text = "No Duplicate";

                  }

              }

              oSqlConnection.Close();

          }

      }

      catch (System.Exception oException)

      {

          FCTools.ShowMessage(oException.ToString());

      }

  }

  いいえ 1 はい

サインインしてコメントを残してください。