I'm afraid you are going about it the wrong way and you need more tables to sort things out.
Breaking up your records so that you do not have nine different fields for nine different events, but rather one field for each event is the key change, but other modifications will also help you out.
Take a look at the invoicing starter solution or the much simpler demo file Comment created for the FileMaker community: http://fmforums.com/forum/showpost.php?post/309136/
I suggest you add a table for clients. One record for each client and an auto-entered serial number to produce a clientID for your relationship to other tables.
Then a table for each type of gift card you distribute. This is the "products" table in the two example files I've mentioned.
Then a table for each time you give one or more gift cards to a client. This is the "invoices" table in the example files.
Finally, you need a table, LineItems to list each type and quantity of giftcard given to that client on an "invoice" record.
I see, thanks for the helpful example.
I have succeeded in setting up the database similar to the example, works great. A related question:
Is there a way to summarize the payment amounts for only the first 6 of 9 rows in the portal I created?
It depends on what makes the first 6 rows significant. Do they have any value in common or could they perhaps be given some common value?
If so a separate relationship that includes that common value could be used so that you can get such a "sub total".
I hesitate to go into any detail, without understanding what is significant about those first 6 rows...
he first 6 rows on the invoice record payments to the client, and then next three record bonus payments to other contacts related to the client. T
These payments need to be recoreded on the same invoice and line items tables, but I'd like to separately sum how much was paid directly to the client.
I suppose I could create a separate field denoting whether payment was given to "client" or "contact". If so, how would I set up the calculation?
Adding that extra field sounds like a good approach. It has the added benefit that you are not locked into 6 rows for the client and additional rowsl for recipients related to the client.
If you look at how the two example files print an invoice, they do so with out using a portal like the one found on the data entry layout. If you print from such a layout, you can add a sub summary part "when sorted by" this added field. Then you can put the summary field already defined to compute the total for the invoice inside this new layout part.
If you then sort your line item records by this field, you'll get a sub total for each group of records that make up your invoice.
From the data entry screen, you can add a pair of filtered, one row portals (FileMaker 11) that display this same field from LineItems. You can set up a portal filter expression such as LineItems::Recipient = "client". To get these totals to update, however, may require a script trigger on any fields that may modify this total so that the record is committed and the window refreshed each time you exit such a field.
It's also possible to set up calculation fields and extra relationships to the LineItems table that only link to "client" and "contact" recipient labeled line items and then these calculation fields can use the sum function to return the same total. This approach require more setting up to do in terms of table occurrences and relationships, but updates more smoothly if you need to see these sub totals on your data entry layout.
Great, I'll try one of those. You've been very helpful!
I have an unrelated question about exporting. I have a very large database with 1100 fields defined, but will need to export the data in all of these fields on a monthly basis. My export file keeps getting truncated, whether I export in Excel or in DBF format. What should I do?
Which Excel file extension are you using and does it make a difference? (.xls or xlsx?)
We had to export 6 months of data to excel here to keep a regulatory agency happy with us. We ended up having to export data in sections to multiple file copies. I assumed that there was an upper limit to how many rows an .xls file could contain.
Have you tried exporting to a text file instead? (most systems that can import from excel or DBF can also import from tab or csv files also.)