Different table using same report layout

[derek0308]

Hello,

I want to write the script that print the same structure report from different tables(Not combined tables to 1 report).  Like Invoice, Proforma invoice to same report layout.  Just press the button then directly print the certain record.

Thanks!

 

[rwoods]

Hi there,

You can't have one layout showing data from two tables. As I see it, you have two options :-

Create the layout you like in the 'Invoices' table, and then duplicate that layout, base the new layout on the 'ProForma' table, and change the 'Display data from...' option to show the 'ProForma' fields.

Then when you click a button to run the script that prints the invoice, the script will go to the correct layout and print the invoice.

This does mean having to maintain two layouts however, which could be tedious.

A better way would be to have a single table, just called 'Invoices', but each record would have an entry in a field called 'invoiceType' that is either set to 'Standard' or 'ProForma'. Then you could have a single printable layout, and you would have some fields or merged text that change to reflect what type of invoice it is.

It is always better (in my opinion) to have as few tables as possible, and where you have entities that share much of their structure (like your 'standard invoices' and 'ProForma invoices'), keep them in the same table with some sort of flag to differentiate them.

You may not want this latter option however, as you said "(Not combined tables to 1 report)".

Hope that helps.

[Kris M]

Hi there,

You can't have one layout showing data from two tables. As I see it, you have two options :-

This is not true. A layout can show any field from the table occurrence the layout is based on AND any field from any table related to the base TO of the layout

[LaRetta]

A layout can show any field from the table occurrence the layout is based on AND any field from any table related to the base TO of the layout

That is true, Kris, but Rwoods rightly suggests that a single layout can not hold fields from two primary entities of equal cardinality where each is a record in the body but from different tables.

Fields on a layout can only be based upon a single table occurrence.  You cannot have fields switch their *underlying table.  That is, unless one uses a temporary table and writes the field data to these generic fields and then bases the report on the virtual table.

I always use a single table as well, identifying whether Quote, Commercial invoice, Credit Memo or Proforma.  It is far easier to omit certain types of records (quotes or pro forma not yet shipped) and summarize only legitimate invoices than to attempt to replicate a quote (and its line items) into an Invoices table.  Each time a record is handled, it opens up possibility that it will break.  And of course it eliminates the problems with attempts to pull 'like' entities (in this case invoices) into a single table for reporting.

It can be done with virtual report but far easier and safer to add a Type field in your Invoices table.

* of course I've seen some folks create calculations of all their fields and then the unstored calculation fields are used on the layout.  It is extremely messy and slow.