Formula on double posting of invoice

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é

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

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 don’t get output, might be that I somehow make a simple mistake but I don’t 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é

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.