Multiple reference proxy fields help
I have included the link to a related thread for this topic. I just need additional info/help and have described my issue under the link
Here is a summary of what I need to accomplish:
I have an EXPENSE leader table. Each expense can have multiple Dates of Service listed.
For example:
Expense #1 has 3 dates of service (similar to a line item) and a total due of $5000.00
Each listed expense below can/does have multiple related LINKS to it.
Date of expense = 2/26/24 Total $ 1500.00 -
Date of expense = 1/23/24 Total $ 1500.00
Date of Expense = 1/1/24 Total $ 2000.00
I have a Links table that is the joining table between my INVOICES table and EXPENSES tables. Invoices and Expenses are both parents to the links table.
The image below shows the links relationship fields in use.
I need to be able to relate multiple client invoices (Invoice table) to different Dates of Service on the Expenses table via my LINKS table. I have this set up right now, however I can only link the invoices that have the first Date of Service in the expenses table. I need to be able to add some way to filter and connect the other Dates of Service on the Expense to those related records in my INVOICES table.
How would I achieve this?
------------------------------
Kelly Lyons
------------------------------
Kelly,
Lets make sure I understand the business case otherwise the advice is useless at best .
- You receive a bill from the vendor, which creates an Expense record.
- The line items on the bill are entered into the Dates of Service table
Bill #1234 from Vendor ABC
Line Item 3456: Date 2/26/24 Total $ 1500.00
Line Item 3457: Date 1/23/24 Total $ 1500.00
Line Item 3458: Date 1/1/24 Total $ 2000.00
Each Line Item has to be Invoiced to only one Client. In this example all three Line Items might be different customers on different Invoices or might go on the same one but your Clients PDQ and XYZ will NOT both have to pay for 3456?
If I have stated this correctly, then the relationships will work. If I am wrong then you need a more complex architecture.
------------------------------
Don Larson
------------------------------
