Forum Discussion

sapphire14's avatar
sapphire14
Qrew Member
23 days ago

Records that contain several sub items

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%

 

 

  • 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's avatar
      sapphire14
      Qrew Member

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

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

    • sapphire14's avatar
      sapphire14
      Qrew Member

      We just need a certain view of the form and report, see photos. First, they create the PO and add the Cost Items. Then there is a button Add Bill showing the Bill Form where they should see the list of Cost Items in the grid edit of the embedded report (is this possible?) and be able to add Bill %. The embedded report is from the Cost Items table and doesn't work because the Bill % will only show the latest update instead of showing the different Bill %. Don't mind the Line Item Bill Summary field and empty fields in the form for now. Just need help on how to make this view possible where each Bill record shows the list of Cost Items with Bill%. Examples are Bill#1 ($85) and Bill#2 ($165). So this PO record has 2 Bill records.