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

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
8 months ago
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
------------------------------

DonLarson
Qrew Elite
8 months ago
Kelly,

It sounds like you need this:

Each Expense grows with additional Dates of Service

The 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
------------------------------
- KellyLyons1
  Qrew Cadet
  8 months ago
  Each expense (our AP) we receive has many dates of services listed that we need to pay to the vendor.
  
  Each "line item" or date of service that we are billed for needs to be connected to several individual/different client invoices (AR) by the date of service.
  
  So would I be setting up a date of service table to be used only as a join table between invoices and expenses, using the Date of services? Then I would use my Links table to connect my expense to my client invoices?
  
  ------------------------------
  Kelly Lyons
  ------------------------------
  - DonLarson
    Qrew Elite
    8 months ago
    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
    ------------------------------

Forum Discussion

Multiple reference proxy fields help