I have made a very simple invoice system: a customers table with customer number as the key; an invoices table with invoice number as key, the invoice date, and customer number linked to customers table; and an invoice items table with item number, item description, price, and invoice number linked to invoice table, as a subtable on the invoices form.
My question is, how can I get a grouping report that is grouped by customer name that shows all items each customer bought over a date range? It has to show each item description and price.
Currently, reports can only be generated based on the stand-alone fields instead of fields in subtable. Hence, you may need to use new sheet from subtable first. Then, generate the report based on the newly created sheet.
If you need to add more fields from parent sheet, you can follow this method.
Thanks, now I can create a new table from the items subtable to display my individual items, and I can also add fields to my report from the invoices sheet. I can’t see how to group or sort by those fields however. How can I group the items by customer or by invoice date?
May I know if you would like to group by the field that you added with link fields from parent sheet? If that’s the case, please follow the steps below:
Step 1. In your original sheet, please add a new free text field, “Customer-sub” field, at the end of you subtable
Step 2. Apply a formula on “Customer-sub” field so that the field value will equal to your “Customer” field
Let’s say you “Customer” field locates in A2. Then, please input formula A2 in the “Customer-sub” field.
Step 3. Manually trigger the formulas recalculation for the formulas you apply in step 2.
Step 4. In the child sheet, please use link fields from parent sheet and add the “Customer-sub” field to your sheet.
Step 5. Generate the grouping report by the “Customer-sub” field.
As for the invoice date, you may create a date field in step1 and follow the rest of the process.
I can add the fields in the child sheet by using link fields from parent sheet, and the customer name and invoice date do appear in these fields, but when I try to create a report from that sheet, the linked fields do not show up to select for grouping or sorting, only the original fields from the subtable are there.
Hi Angie, I found what I was missing. After I link the Customer-Sub field from the parent sheet, I had to go back to the listing page, change design, use the field picker to add the field to the listing page, and save the change. After that, when I went again to create the grouping report, the Customer-Sub field became available in the grouping and sorting boxes. If I remove the field from the listing page, it again disappears from the report grouping and sorting choices. Thanks for your help!
