I am really stuck on this and I think its do do with the tables and relationships.
I have the following tables that are relevant
CLIENT (PK is ClientID)
who has a
QUOTE / INVOICE (PK is Client ID, Invoice ID)
(same entity just a different status on the record)
the work performed is at a Client
LOCATION (PK is ClientID, LocationID)
A Client has many locations
A Client as many Invoices
An Invoice has one Location
An Invoice has one Client
Some of the clients are Real Estate agents and have many properties so I have a LocationName filed on the QUOTE / INVOICE table so that when the document is emailed to the Client it will show the LOCATION related to the work. I am struggling to expose the LocationName field from the LOCATION table on the Invoice layout.
I can get it to work if I configure the Value List (from field) to show all values. However I only want to show Locations for the Client the Invoice is for.
The app logic is
Step 1 - open a Client record on the Client layout and then press Create Invoice button
Step 2 - now on the Invoice layout with a new Invoice record, with Client Name prepopulated (simple, just like the Invoice starter app from FM12)
The Data viewer shows the correct Client ID in the Invoice table (as picked up from the Client table). This is obvious as the Client Name correctly displays on the Invoice layout.
I have a relationship defined on Client ID between CLIENT and INVOICE.
I have tried creating a relationship between INVOICE and LOCATION but with or without it, the list of locations is either empty when I use the "include only related values from ..." option or shows all records with I dont.
I have tried using a relationship for both directions.
- Invoice (Client ID) > Location (Client ID)
- Location (Client ID) > Invoice (Client ID)
This produces the alias table and when I add the Client ID attribute of that table to the Watch list it has no value. Clearly thats related to the problem, but I have no idea if its a cause or symptom.
Any help would be appreciated.