Hi everyone,

I'm developing an invoicing solution and have run into a bit of a problem.

In my customers layout i want to see both the invoices and the line items related to that invoice. My thinking is to create a portal that displays all of the customers related invoice records and, based on the selection made by the user, have a second portal populate with the invoices related line items.

I've created a script that sets a global text field to the primary key of the selected invoice, the next step is to filter the second portal based on that field, something like this:

LINEITEMS::_kf_invoices_id = CUSTOMERS::xinvoice_id

obviously this isn't working, otherwise i would be posting about it :

Does anyone know what i'm doing wrong or have a better solution? It's getting hard to see the forest through the trees so any help is appreciated.

Your relationships should be something like:

Customers -> Invoices -> LineItems -> Products

If so, just put a portal for Invoices on the customers layout and it should display the related invoices for the customer.

To display the line items for a selected invoice you need another TO in the graph:

Customers -> LineItems_Selected

... where the relationship uses the InvoiceID_g global field in the Customers table and the InvoiceID in the LineItems. A script is needed in Customers to put the appropriate InvoiceID into the global field.

I'd add a script trigger to the layout to clear the global field when the record changes otherwise the line items will still display after changing to another client record.

Try adding a Refresh Window [Flush cached join results] step to your script.

See also:

http://fmforums.com/forum/showpost.php?post/364244/