Checking a Unique Key against Existing Ones in Many to Many relationship

[vitbyst]

For the life of me I don't understand why it does not work

I am redesigning my invoice database that originally was done in FM 6, but now making it in 8.5

Before, I had three files: Invoices, Fees, Invoice Items.

In order to avoid adding duplicated invoice items to Invoice, I had a calculation as a unique ct_invoiceID_feeID_key ( invoice_id & "-" fees_id) set up in Invoice Line Items file.

Upon adding a new invoice items, the script was setting up a global field in Invoice Item file with that unique concatenated key of a new invoice items I am about to add, and then invoking a script in Invoice Items files to check this global key fee against keys of existing invoice items ( Using script

If ( Count( ct_inv_fee_key ) > 0)

Exit Script

See the attached screenshots.

Now, I am using variables, and yet i cannot get this to work. No matter if I add another fee or an already existing on the invoice one, Count calculation doesn't work, it always finds an existing key. I tried using

If ( $invoice_fee_key = Inv_line_items::ct_inv_fee_key )

Still no go. I realize it doesn't search of matching keys.

I figured a work around using Loops, and searching for a key, but it is too cumbersome. I am surely missing something.

[mr_vodka]

Why are you going to inv_line_items and doing a count on ct_inv_fee_key? This only checks the current record that you are on in inv_line_items.

[vitbyst]

because I am not sure what I am doing? :'(

In old file format I had a script brought up from Invoice Line Items file to execute that Count calcluation.

Besides, it does not work even if I take "Go to Invoice Line Items table" out. I am confused.

[mr_vodka]

can you post a copy of your file?

[vitbyst]

here is a simplified copy. thank you!

I need to check if such a unique key ( invoiceID & feeID) already exists in this invoice. I can create a field validation in invoice items, but how I can gracefully do it with a script? And why it worked before in FM6?

inv_fee_invitems.fp7.zip

[mr_vodka]

I would probably approach it like this.

inv_fee_invitems_redo.zip

[vitbyst]

Thank you.

Sorry, I am late with replying: was internetless for a few days.

It is very interesting solution. I will try this and also try out recreating the older solution using globals rather variables.

Thank you again