Creating an Invoice Style Layout

[PHF]

I have attached a sample database to illustrate my question.

The database has 4 layouts. I would like to combine the data in layout Order, Products and Summary to create an invoice like the layout "Invoice"

I have provide some sample data in the database

Test DB.fmp12

[comment]

This seems to be a repeat of your previous question:
https://fmforums.com/topic/109481-filemaker-19-relationships/

 

[PHF]

Yes this is somewhat of a repeat of a previous question but I have combined the three databases of the original design into one database with three tables.

I have read the links from filemaker that you provided (thank you for that)

Could someone help out a newbie and show me their version of how to setup the many to many relationship in my database.

I need the data from the products and summary tables to flow into the portal of the orders portal table.

I am also trying the get a simple invoice setup to display only what was ordered.

I think once I see it visually it will make more sense to me.

Thank you to anyone in advance for helping me with this.

 

[comment]

13 minutes ago, PHF said:

how to setup the many to many relationship in my database.

I wouldn't know how to setup the relationships in your database because I don't understand your database. I have no idea what a "cog number" is and I don't know what does a record in the Summary table represent in real life.

A typical solution dealing with orders will have tables for Orders, Products and LineItems and the relationships between them are explained in the links I have provided a year ago (to the day, as it happens).

If you have some extra requirements beyond that, you will need to explain exactly what those are.

 

 

[PHF]

The cog number is the line number assigned to the order

I have attached 3 spreadsheets. The orders we receive come from an online form we use.

There are 3 spreadsheets that are used to compile the order info. Orders, Products and Summary.

The Order Info is the Name, Address etc.

The Products are what was ordered

The Summary is the dollar amount they paid.

You will see that on the products spread sheet and the summary spread sheet that the line number can be listed a few times which is why I went the portal route.

Thank you for helping me with this. Much Appreciated.

DanceSchoolOrderForm2023PROOFS-Order.csv DanceSchoolOrderForm2023PROOFS-products.csv DanceSchoolOrderForm2023PROOFS-summary.csv

[comment]

Sorry, I still don't get it. 

49 minutes ago, PHF said:

The Products are what was ordered

The Summary is the dollar amount they paid.

Why aren't these in the same table to begin with? What I see is that the Summary table lists both what was ordered and how much they paid for it (which makes it effectively the LineItems table). The Products table seems to have some additional info about what was ordered - but I see no value that could be used to link a line in the Products table to the corresponding line in Summary. 

 

[PHF]

The company works in the photography industry and the image number is a vital piece of information that needs to be included on the Invoice. The image number is only on the products spreadsheet. The customer can supply multiple image numbers in their order

[comment]

5 minutes ago, PHF said:

the image number is a vital piece of information that needs to be included on the Invoice.

If that's all you need from the Products table and there is no need to link an image number to a specific line from the Summary table, then you could just use a calculation field to fetch the image numbers. There's a minor complication caused by having 3 columns for them, but that could be easily solved (assuming there will never be more than 3). 

In such case you could use the relationships already defined in your file - except that I would rename "Cog Number" to OrderID and validate it as unique in the Orders table.

 

[PHF]

There could be up to 10 image numbers. Can you suggest a calculation for this.

[comment]

I assume you intend to import these periodically, so if there can be up to 10 image numbers, you will need to define 10 image fields in the Products table (you only have 3 now). Once you have done that, define a calculation field, also in the Products table, cImageNumbers (result is Text) = 

List ( Image1 ; Image2 ; Image3 ; Image4 ; Image5 ; Image6 ; Image7 ; Image8 ; Image9 ; Image 10 )

And then another calculation field in Orders (result is Text) =

Substitute ( List ( Products::cImageNumbers ) ; ¶ ; ", " )

This will give you a comma-separated list of all the images that belong to the order.

WRT to importing, you should have something to prevent importing the same data twice. In the Orders table this is easy to do by setting the OrderID to validate as Unique, Validate always. But I don't know if you have anything similar in the other tables (I certainly don't see anything that looks like unique in the Summary table).

Whoever designed this has a lot to answer for.

 

Edited December 31, 2023 by comment