Skip to main content
Question

Formula on double posting of invoice

  • December 30, 2019
  • 3 replies
  • 3 views
R
Level 1
Hello, I struggle with a the creation of a formula to determine double postings. (in this cases potential double vendor invoices posted.) My check is to see all case ID (invoice items) with the same invoice reference field (text field) and the same amount. Out put of the formula should be number of cases where this duplication appears. Anyone who has an idea how to create such a formula? Thanks! Kind regards, Ren

3 replies

V
Level 0
Hi Rene, you can try to count the cases which have the same invoice reference and amount using the DOMAIN_TABLE function: COUNT(DISTINCT CASE WHEN PU_COUNT(DOMAIN_TABLE("INVOICE_TABLE"."INVOICE_REFERENCE_FIELD"||"INVOICE_TABLE"."AMOUNT"), "CASE_TABLE"."CASE_ID") > 0 THEN "CASE_TABLE"."CASE_ID" ELSE NULL END) Best regards, Viana
R
Level 1
  • ren.ren
  • Author
  • Level 1
  • February 10, 2020
v.kalversberg: || Hello Viana, thank you for the respond, I think this kind of formula I am looking for! Only when i add the formula it seems like it dont get output, might be that I somehow make a simple mistake but I dont see it. Can you see based on the formula why I get the error that the table parameter has not been properly set? *COUNT(DISTINCT * *CASE WHEN * PU_COUNT(DOMAIN_TABLE("_CEL_P2P_CASES_NEW".LIFNR||"_CEL_P2P_CASES_NEW".NETWR_CONVERTED), _CEL_P2P_CASES_NEW."_CASE_KEY") > 0 THEN _CEL_P2P_CASES_NEW."_CASE_KEY" ELSE NULL END) Thanks for your support! Kind regards, Ren
R
Level 1
  • ren.ren
  • Author
  • Level 1
  • February 26, 2020
Thank you Viana., your proposed solved my issue. I found the reason why the formula was initially not working as expected. We first needed to upgrade from version 4.3 to 4.5 to be able to execute the domain_table function.
Community Code of ConductAccessibility statement