Records that contain several sub items

Question

I have a Purchase Order (PO) table connected to Cost Items (CI) table. In the PO form, there is an embedded report for users to easily add items (see photo 1).

There is also a section in the form for Billing that shows the same embedded report where they can input Bill % and all of these bills should be in the Bills table. Every Bill record can contain any of the cost items assigned with Bill % until it reaches 100%.

For example:

Bill #1 - contains CI#1 with 60%

Bill #2 - contains CI#1 with 40 %, CI#2 with 50%, CI#3 with 70%

Bill #3 - contains CI#2 with 50%, CI#3 with 30%

markshnier__you · Answer

I'd like to help but I'm not really understanding your 2nd embedded report and what your specific obstacle is. Can you explain further?

sapphire14 · Answer

Hi there. The second embedded report is the same one under PO Cost Items section except there are additional columns like Line Item Bill Summary, Bill % and Bill Amount. Currently, this doesn't work because for every Bill % assigned to a cost item, it should create a record in the Bills table. I need help how it should be set in the Bills table with this example below. So it's like every Bill record can contain 1 or more cost item with the assigned Bill %. Let's say today, I wanna bill for cost item #1 for 60% then the next day, I wanna bill for cost item #1 for 40% (already 100%), cost item #2 for 50% and cost item #3 for 70%. These are all under a certain Purchase Order (PO). I should be able to do the same for any PO as every PO can have more than one Cost Items.

Bill #1 - contains CI#1 with 60%

Bill #2 - contains CI#1 with 40 %, CI#2 with 50%, CI#3 with 70%

Bill #3 - contains CI#2 with 50%, CI#3 with 30%

donlarson · Answer

What is your table structure look like?&nbsp; How are these connected, Parent, Child and the Many to Many?

sapphire14 · Answer

The Purchase Orders table is a parent table to Cost Items and Billing table (see photo #2). For users, they need to log a PO record first where they also add the Cost Items using the embedded report. Then they can go back later to create a bill where they can bill for say first 2 items and at a certain Bill % or both at 100% (see photo #3). The end goal is to be able to fully bill (100%) on every cost item.&nbsp;

donlarson · Answer

You built this:&nbsp;I suggest that you build this instead:&nbsp;Assume you have a single Cost Item at $100.You want to invoice the customer 50% when the PO is issued, 25% when the project starts in the field and the final 25% when it completes.Therefore you will need three records in the Billing table and three records in the Billing Line Item table for each of those %'s.&nbsp;&nbsp;Create a Summary field between Cost Items and Billing Line Item to show you how much has been Billed.&nbsp; &nbsp; You can even make that a hyperlink to a report that will show you when all of that happened.&nbsp;

Forum Discussion

Records that contain several sub items