How to use a record picker to select an existing child from the parent add/edit view?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
  • (Edited)
Originally posted in the former community durning migration:
  • Asked by Sarah
  • a month ago
I have a parent table (Lines) and a child table (Invoices). On the Invoice add/edit/view page, I want to display a record picker of Lines instead of Add Line. Users will always create a Line, then associate it to an Invoice later. How do I do this?

Also, will I run into issues with an Invoice having multiple Lines, and multiple Lines belonging to multiple Invoices? i.e. A Line will be created when the order is placed, and then there will be a monthly Invoice for multiple Lines, thus each Line will be used across multiple Invoices, and each Invoice will have multiple Lines.



~~~ UPDATE ~~~ due to response

I now have Invoices, Lines, and Invoice Lines. An Invoice Line has one Invoice and one Line. On my Invoice Form I have

[Add Invoice Line] --button

[Related Invoice Lines] -- Embedded Table

When I click on [Add Invoice Line], a new window loads that shows me 

[Related Invoice] (with current Invoice selected)

[Related Line] (record picker)

I am updating the [Related Line] form element, using "Embedded for Invoice Line" report. 

My Question: How do I filter this report to only include records where the Line and Invoice share a vendor/account?

 
Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb
Answer

This sounds like a many to many relationship. I suggest that you rename your Lines to be referred to as Order Lines. Then you will have your current invoices table. Then you will need a new table called invoice Lines. One Order Line will have many Invoice Lines, One Invoice will have many Invoice Lines So in fact, you want to be adding (creating) Invoice Lines when you are making an invoice.
Was this answer helpful?

Answered by:
Original
QuickBaseCoach App Dev./Training , Mark Shnier, Principal
QuickBase Solution Provider a month ago

8 comments

Why can't I have 2 relationships: Invoice to many Lines, and Line to many Invoices?


When you go to cut an invoice, you will need to be entering values like the the amount to invoice.  Where would you enter that data?


The amount and due date and such would be on the invoice. The invoice also has an invoice id and several lines it's paying for. While the line would include values that describe the service being paid for - Qty, price / unit, vendor, etc. A line should list the invoices its been listed on.


My point is that you will need that middle table of invoice lines because they are going to have data on those invoice lines which will vary from invoice to invoice, for the same Order Line. The Invoices Table will have an embedded report of the Invoice lines and those lines can include any lookup fields from the Order Lines.


How would this work from the user's perspective? They want to be able to create a line, and then later when creating an invoice, select multiple lines (or invoice lines) and see all of the selected ones. They should only see the lines with the same parent vendor.

Would the Invoice Line table be an unseen table with only 2 fields - Related Order Line & Related Invoice (plus the base - created by, etc)? Then in the Invoice form - display an embedded report of Order Lines that are in Invoice Line and have this Invoice as the Related Invoice? I'm not quiet sure how this would work behind the scenes or for the user.


Yes, your second paragraph is correct, unless you needed to have some fields on that middle join table. The users would not really see that middle table. I mean they would see it but not really realize that it is a separate table. I'm still not sure if the join table would actually not need to have any extra fields on it in your use case. I have only seen use cases where that middle table does need at least 1 field


Thanks. Going great. Updated my question above - how do I filter my embedded report?

OK, I'm responding to your updated Question. I suspect that once you have this working you will say that QuickBase is wonderful.

When you Create an Invoice, no doubt that Invoice is connected to the Customer.  You seem to call that Vendor/Account.  The field that connects the One Vendor to Many Invoices is typically called [Related Vendor].

Look up that field to the Invoice Lines table and call it [Record ID# of Related Vendor].  For now, put that field visible on the bottom of the form.

Go to the field for Related Line in your table of Invoice Lines and look for the checkbox called
Conditional values The values in this field depend on a selection in another field

Check the box and set it up so that after selecting a value for Invoice Line; Record ID# of Related Vendor
Only show values which match Lines: Related Vendor

Once you have this working, then you can hide that field [Record ID# of Related Vendor] by putting it in a section called Reqd for Form Rules and Cond Dropdowns and use a form rule like

When [date created] is not equal to 99999999
Hide section Reqd for Form Rules and Cond Dropdowns