Forum Discussion

Qrew Trainee

8 months ago

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

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

KellyLyons1

Qrew Cadet

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

KellyLyons1
Qrew Cadet
8 months ago
From your example:

Bill #1234 from Vendor ABC

Line Item 3456: Date 2/26/24 Total $ 1500.00 (6 related client invoices)

Line Item 3457: Date 1/23/24 Total $ 1500.00 (5 related client invoices) etc

Line Item 3458: Date 1/1/24 Total $ 2000.00

So each line item in your example, will have many related client invoices - which I currently connect right now through my links tables -

What I am trying to figure out is how to connect the 2nd and 3rd line items on the example above in the same way. I have this already working using 1 date of service, I am trying to figure out how to connect the other dates of service to the many related client invoices.

Does that clarify?

------------------------------
Kelly Lyons
------------------------------
- DonLarson
  Qrew Elite
  8 months ago
  Kelly,
  
  Line Items in the Dates of Service is a Many to Many on Invoices. This will let you do that.
  
  Sticking with our example:
  
  Line Item 3456 can be on an unlimited number of Invoices for every Client in the System. Naturally you will want some sort of rules to prevent a Line Item from this month going on an Invoice from 2023, but you could do that if you wanted to.
  
  You can build a report in the Invoice Services table that you can embed into the Dates of Service table. It would show something like this.
  
  3457: Date 1/23/24 Total $ 1500.00
  
  Client Invoice Number Invoice Date
  
  IBM 123456 March 15 2024
  
  Oracle 123460 March 16 2024
  
  Google 124751 March 17 2024
  
  Invoices # 123456, 123460 and 124751 might also have dozens of other Line Items on them, but all three have the Line Item 3457.
  
  I assume you are not billing the whole $1500 to each so you will need another field in the Invoiced Services table for their share of the $1500. If you are billing the whole thing, I want to learn more about your business. :)
  
  ------------------------------
  Don Larson
  ------------------------------
  - KellyLyons1
    Qrew Cadet
    8 months ago
    Don,
    
    Thanks for your help. I am going to try and implement this tonight in a copy of the app to be sure it works the way we need it to. Fingers crossed!!!
    
    ------------------------------
    Kelly Lyons
    ------------------------------