Please can you help explain how I can filter the choices on a subform lookup field based on an entry in a field on the main form and at the same time keep the subform row choices in the Main form report so I can analyse them and calculate a total.

I am very new to this!

I am creating an invoicing application. I have created an Invoice form. I have worked out how to select a customer from a customer database. Each customer has an insurer, but the amount I can bill the customer for different tests or services varies depending on the their insurer

I have a Test form into which I have put the various tests and services I do, with the corresponding fee for each one based on each insurer. Each test therefore has 8 entries as there are 8 insurance companies and there are 6 different tests, so there are 48 records in the Tests form. I may do one or more tests for each customer, who may visit more than once, meaning I may issue more than one invoice to the customer over a period of time.

In my Invoice form I have a lookup for the customer which also populates the main form Insurance company field. I then copy the insurance company name into a hidden string field in the Test subform to use later 

In the Tests subform I have worked out how to filter the tests lookup so it only shows the tests for the relevant insurance company and auto populates the amounts field. The script I've used is 'on add row' for the Tests subform field in the invoices main form

newList = (FORM name of source that Test lookup field goes to[FIELD name containing Insurance Company criteria in source form.contains(input.FIELD name in main form that contains the Insurance company name)].ID).getall();

clear row.Test FIELD name in subform;

row.Test FIELD name in subform:ui.append(newList);

It works! but when I look at records in the Invoices Report, all I see is the customer details data entered into Invoice Main Form, plus the invoice date, but it doesn't show the Tests subform records.

After more exploring I realised I have to create a relationship between the subform and the main form to link the test records with that unique invoice record. I did this by adding a lookup field to the Tests form and creating an existing relationship with the main, Invoice form and choosing the ID field

The problem is, that has stopped the filtering from working, so now when I try to add to the Tests subform I get a huge list of each of the 48 entries in the Tests source form with all the insurance companies, not just the 8 tests for the one which is listed in the Main form and linked to the customer.

I don't understand why and would appreciate a simple, easy to follow explanation

Many thanks