Consolidating tables in one view

[maretec]

 I have 1 parent table and 3 child tables.  The parent table is "Reclamo".  The child tables are "Facturas", "Fletes" and "Ajustes".  All child tables have a Ref type column with the "Reclamos" table as the source and the option "is part of" selected.  The screenshot below shows the "Related" columns (Related Facturas, Related Fletes and Related Ajustes).

Facturas, Fletes and Ajustes are charges related to Reclamos.  These 3 tables have a Yes/No type column to indicate whether the item has been paid. There is also a Date column for the item's date and a Price column for the charge in all three tables.

I want to create a Statement of Account so I can view facturas, fletes and ajustes that have not been paid and group them by their respective "Claim" record (see screenshot below).

Also, I have to create a PDF similar to this view.

Any advice on how to make the view and the PDF would be welcome.

[Suvrutt_Gurjar]

I think there is no user friendly native way to display records from multiple tables into one view.

You seem to be having identical ( or at least many columns being common) column structure for child tables  Facturas, Fletes and Ajustes

You may want to evaluate if you could have just one related table with additional enum type column of [Item_Type] with enum options of Facturas, Fletes and Ajustes.

This will allow you to show the related Item types in one table. However please thoroughly analyze  this option of combined table after evaluating your entire app design and requirements. We from community may not know all other requirements of your app that may have made it necessary for you to have three different child tables with almost similar column structure.

[maretec]

Thank you for taking the time @Suvrutt_Gurjar.

Let me give you more details.

A vehicle repair shop must send statements to the insurance companies it works with. When a vehicle repair is approved, the insurance company uses a claim number to identify everything related to the vehicle's repair.

There are 3 charges made by the mechanical workshop: invoices (labor), spare parts freight and adjustments (these are additional charges to the invoices). A claim number always have at least one related invoice.  The combination of charges are: 1 or more invoices; 0, 1 or more freigths; 0, 1 or more adjustments.  Since there can be more than one invoice, freigth or adjustment for one claim number, there are 3 child tables (Invoices, Freigths and Adjustments) and a parent table (Claim).

Statements must show the Claim Number and its related charges, both in an in-app view and in a PDF, similar to the screenshot below.

Does this help to have a better understanding? 

[Suvrutt_Gurjar]

---

@maretec wrote:

Since there can be more than one invoice, freigth or adjustment for one claim number, there are 3 child tables (Invoices, Freigths and Adjustments) and a parent table (Claim).

---

Thank you for giving more details. However I am unable to think why above requirement can still be not accommodated in one child table. One can technically have the same claim number with multiple item categories ( as identified by the item type enum) in the same child table? 

[maretec]

I created a new table using categories to create the view, but now that you mention categories for the child table, I think it's a better solution.

Let me try it. I'll keep you posted.

[maretec · Accepted solution]

The solution was to create one child table using a "Category" column.  This table has the following columns: ID (Key), Claim (Ref to Claim table), Category (invoice, freigth or adjustment), and all other common columns to invoice, freigth or adjustment like date, amount, etc.

This allowed me group all the invoices, freights and adjustments under their respective claim using the "Group by" option in the view.

Thanks @Suvrutt_Gurjar 

[Suvrutt_Gurjar]

Thank you for the update. Good to know the suggested approach of creating one table with item categories worked. 

---

@Suvrutt_Gurjar wrote:

One can technically have the same claim number with multiple item categories ( as identified by the item type enum) in the same child table? 

---