Multiple reference proxy fields help

Question

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
https://community.quickbase.com/communities/community-home/digestviewer/viewthread?MessageKey=a3c3baa3-807d-4295-ac20-4f7dc5250cd0&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

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------------------------------

DonLarson · Accepted Answer

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 tableBill #1234 from Vendor ABCLine Item 3456: Date  2/26/24   Total   $ 1500.00 Line Item 3457: Date  1/23/24   Total   $ 1500.00Line Item 3458: Date  1/1/24    Total    $ 2000.00Each 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------------------------------

DonLarson · Answer

Kelly,It sounds like you need this:Each Expense grows with additional Dates of ServiceThe Expense is not Invoiced at once but instead the Dates of Service go on Invoices for the Client with other Dates of Service from different Expenses.This way a single Invoice can contain the Dates of Service from many Expenses.    ------------------------------Don Larson------------------------------

Sign up

Login to the community