I have an invoice file that I use to put products on an invoice and send to customers. I have a header, body and footer. The problem I'm having is figuring out how to only show so many repetitions of the product fields. If I only have one product I just want to show one row. If I have 25 products I want to have 25 rows. If I have 25 rows it spans multiple pages with the footer and the bottom totaling the rows. How do I do this? I've attempted with parts but I just don't see how it can be done.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Repetitions
- Thread starter fiber0pti
- Start date
fiber,
I assume you are using repeating fields for your line items. If this is the case then you are going to have trouble. Repeating fields were used in the early days of Filemaker before it (Filemaker) went relational. Personally I haven't used a repeating field in many years. If you are using these repeating fields then you have a fundamental problem with the structure of your database. If you were to structure (or restructure) your database to utilize a header file with a related line item file you would be able to accomplish what you are asking for. You would also be able to more granular reporting too.
What version of FM are you on? Is this a new design or has it been around for a while?
-Striker
I assume you are using repeating fields for your line items. If this is the case then you are going to have trouble. Repeating fields were used in the early days of Filemaker before it (Filemaker) went relational. Personally I haven't used a repeating field in many years. If you are using these repeating fields then you have a fundamental problem with the structure of your database. If you were to structure (or restructure) your database to utilize a header file with a related line item file you would be able to accomplish what you are asking for. You would also be able to more granular reporting too.
What version of FM are you on? Is this a new design or has it been around for a while?
-Striker
- Thread starter
- #3
You're on v7. That's good.
You need to create two different tables.
a. Invoice_Header
b. Invoice_Line
You will need a primary key field in the header file. Typically it will be the invoice number. For our purposes lets call it Inv_No. It needs to be set so that it is a required field and it's values must be unique. Often it is an auto-numbered field.
In the Invoice_Line file you will need a field for the foreign key. It should be named Inv_No just like that of the header table. It should be a required field but not unique.
Of course you will need all of the other fields normally found on an invoice. Put fields that apply to the entire order in the header table and fields that apply to the individual lines in the line table.
On the relationship graph connect the two tables via the Inv_No field.
Honestly, I could write an entire chapter of a book on how to do this exercise. I don't know how much you know in general about databases but this is the foundation of how it should be structured. It's too involved to go over exact steps to set this up but I'll offer up a couple of things to be aware of.
Take a look at Portals. Portals allow you to see related data (your line items) from a layout that contains your header information.
When it comes time to print your invoices come at it from the line item side. What I mean by this is the body of the layout should contain the line items and the header/footer sections can contain information from the header table.
If you have specific questions please feel free to write back.
-Striker
You need to create two different tables.
a. Invoice_Header
b. Invoice_Line
You will need a primary key field in the header file. Typically it will be the invoice number. For our purposes lets call it Inv_No. It needs to be set so that it is a required field and it's values must be unique. Often it is an auto-numbered field.
In the Invoice_Line file you will need a field for the foreign key. It should be named Inv_No just like that of the header table. It should be a required field but not unique.
Of course you will need all of the other fields normally found on an invoice. Put fields that apply to the entire order in the header table and fields that apply to the individual lines in the line table.
On the relationship graph connect the two tables via the Inv_No field.
Honestly, I could write an entire chapter of a book on how to do this exercise. I don't know how much you know in general about databases but this is the foundation of how it should be structured. It's too involved to go over exact steps to set this up but I'll offer up a couple of things to be aware of.
Take a look at Portals. Portals allow you to see related data (your line items) from a layout that contains your header information.
When it comes time to print your invoices come at it from the line item side. What I mean by this is the body of the layout should contain the line items and the header/footer sections can contain information from the header table.
If you have specific questions please feel free to write back.
-Striker
- Status