Forum Discussion

KellyLyons's avatar
KellyLyons
Qrew Trainee
23 days ago
Solved

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's avatar
    DonLarson
    22 days 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
    ------------------------------

6 Replies

  • DonLarson's avatar
    DonLarson
    Qrew Commander

    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's avatar
      KellyLyons1
      Qrew Trainee

      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's avatar
        DonLarson
        Qrew Commander

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