Summarizing data from repeating fields

[landscaper]

Well, here it goes. this is my 1st attempt in posting a question for help. I hope I am posting this in the right forum.

What I am trying to do is to pull information from a found set of records and calculating the quantity from each repeating field so I know the quantity of individual items that are needed for future jobs. Example; there are 10 invoices each with some of the same items and also different items, I want to know the total quantities of each item in a seperate layout for all other 10 invoices. I have tried summary fields with no solution.

I'm stuck. Any help would be appreciated.

lj

[bcooney]

Are you really using repeating fields on your invoice? If so, you might consider changing to a relational model. Invoices relate to a table of invoice line items by invoice ID. The line items appear in a portal on the invoice record. You print invoices from the invoicelineitem table.

Then, you can easily report using the InvoiceLineItem records quantity used by itemID using sub-summary reports.

[Fenton]

I second bcooney's suggestion. Here is how it's done, fairly easily.

Create the same fields as non-repeating fields in a new table, Invoice Line Items (or abbreviation).

(If you had FileMaker Pro Advanced you could just copy/paste them.)

Create the ID of the Invoice in the line items table (as a foreign key). It will NOT be auto-enter.

Create a primary ID in the line items table. It will be auto-enter serial ID (not really needed yet, but likely will be).

Create a relationship from primary ID of the Invoice table to the line items table.

Check [x] Allow creation of related records, and likely [x] Delete related records, on the line items side!

Import the existing repeating fields into their counterpart in line items. A dialog will ask if you want to split repeating fields into records. Say yes. Include the Invoice ID in the import. Show all Invoice records first. You now have all the data in the line items table.

In Invoice, change your "subtotal" field, which is

Sum ( line total repeating field )

to

Sum ( line items relationship::line total )

Go to the Invoice layout. Double-click each of the repeating fields, change each to its corresponding field in the line items table, using that relationship.

Draw a portal around them, say how many rows (likely same as repeats were).

You can now (after checking) delete all the repeating fields from the Invoice table.

Save a Copy as Compacted (optional).

You will also need a Print layout in line items, if your invoices are longer than a page, if the invoice portal could go over a page break (which can be ugly). This can be fairly easily done by copying your existing layout and pasting into a LINE ITEMS layout.

Put the "Invoice" fields in the Header, Trailing Grand Summary or Footer.

Put what were the portal fields in the body.

Delete the portal itself (if you copied it); it is no longer needed, as this is the portal's fields' native layout.

Tip: You can hold the Alt key (Option on Mac) to allow dragging a part's border past objects.

Now you can fairly easily create a Subsummary layout for your "summaries by item," using a Summary field in a Subsummary part, sorted by Item name (or product ID).

Easy breezy -]

[landscaper]

Thanks for the help so far. I guess my learning curve is about to go north again. When we 1st started using FM it was version 2 i think on a Mac. The repeating field was recommended at that time. Worked for many years but as business has changed now we need use the info differently.

Thanks for your responses. I'll get started and see how it goes. I'm sure there will be more ??'s later.

LJ

[Fenton]

You're on the same path that many of us have travelled. I also started with repeating fields (FileMaker 3), but quickly realized they were limiting what I could do. It was also a landscaping database -]

BTW, the one thing about relational structure. You gain a great deal. But you lose indexing of the totals in the parent table. It is not usually a problem, and there are ways to get it back (more or less, transactional processing, another level of development). But that's the price you pay for functionality.

[landscaper]

Okay, a few more questions. I assume that once this is completed, that the invoice file I am now using will be replaced with the new table and related items or will I still use it?

When creating the same fields in the new table, i am assuming that you are referring to the fields that are in the existing table. Is this correct?

Will all of the calculations, etc still function as they do now?

I'm would guess that these are probably really basic questions but I do appreciate any inlightenment!!

LJ

[Fenton]

The Invoice file/table remains much as it is. The new "invoice line items" table and its fields replace the current "invoice repeating fields", both on the layout and in the "subtotal" calculation in Invoice.

When you've got all the above set up correctly you can delete the repeating fields; after you're sure you've got them and the existing data into the new table correctly. The Invoice should look almost exactly the same as it did before. Read the instructions again.

Edited July 22, 2008 by Guest

[landscaper]

thanks for your help. it finally started clicking this afternoon. i can see how much there is to do to get everything updated now!

lj

[GreenPilgrim]

Fenton:

I'm reading in on this discussion attempting to change a layout from repeating fields to portals. I shouldn't be listed as intermediate, but beginner intermediate so be patient with me please.

On the line where you state:

"create the ID of the invoice in the line items table(as a foreign key)." What do you mean by that? I don't know what an ID is or a foreign key. I went to your web site but am just confusing myself. I figured it would be better just to ask you.

Also, I attempted this once and thought I was doing pretty good, but then I got to the import feature & couldn't get my fields to line up properly, though I had named them the exact same thing as the original table.

I'm looking down lower on the instructions and realize I'll need help later with the portals as I am clueless as to what those are. Your help is greatly appreciated.

[Søren Dyhr]

I don't know what an ID is or a foreign key.

Then change your profile, please! Spreadsheet skills have no bearing here, I'm afraid...

--sd

Edited July 25, 2008 by Guest

[GreenPilgrim]

Please forgive me. I was wrong. The problem with my profile has been corrected. I didn't realize it was important.

Would someone please tell me what an ID is & how to set a foreign key?

Edited July 25, 2008 by Guest

[Søren Dyhr]

Why should be shown if it didn't have any meaning?

Well try to tear this file appart, and see when summary fields are used and when aggregate functions are used, last but not least how the entire relational matter is approached with the utilization of ID's for each record ...foreighn keys and all...

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

--sd

[GreenPilgrim]

I guess that's my problem, I don't seem to have an eye for detail. Sorry that I offended you. Thank you for the information and I'll look at it soon.

Sincerely,

Sister Mitzi