Need To make a REPORT THAT SUMMARIZES TWO FIELDS

[siou]

Hello. I am working on a filemaker system for people who make clothes.

I need to make a report that can lookup values from more than one field, and then return a report that summarizes values from two or three fields in one report, separated by colors, and sizes.

For example:

I will have a report for the amount of zippers used for producing a total order.

In the main database, the user enters the amount of zippers used in two separate fields.

This is because sometimes you need to use more than one zipper to make an item of clothes. I however need to do a production report when someone orders multiples, and then my report needs to look up all the instances when ZIPPER 1 is used in field one, and also in field two, and summarize these on one report subtotaling by color.

oh, i am super stuck on this, and I have to finish soon, so please if you have any ideas for help...i would really appreciate it.

Thank you.

[zincb]

Do you mean, for example, the user says they want 10 copies of 2-zipper dress A, in red, and also 20 copies of 3-zipper dress B, in yellow ... and ... to fill the order, you want to produce a report that shows how many zippers of what color to order? Are all the zippers identical, except for color?

[siou]

Hello, and thank you for your help.

So, there is a main file where the user decided the style of zippers to use for (let!s use for example) RED DRESS. I have two zipper field entries, because normally you will need more than one zipper on clothes. So, for this first RED DRESS, we will need, inputted into FIELD 1: 2 of Zipper1. Then the user decides to make a pair of BLUE PANTS. They use primarily in ZIPPER FIELD1: ZIPPER 2 (not ZIPPER1), and place that they will need 2 Zipper 1 in ZIPPER FIELD2.

So, thats all fine and dandy until later in the system, where i have developed a production report system.

Basically, after entering all wholesale orders, I am trying to make a production system that can ask the invoices data questions...such as...for this group of invoices, how many zippers total do I need?

The problem is here:

To do a report, I have to tell the system where to look, and since the user has inputted different zipper1 amounts into the two separate Zipper Fields, I never will get a true result.

I am sure this is a calculation or a scripting, but I am not too good at these.

I have hand built this system for years, but I am at apoint where it needs to be better, and I am confused.

So, I would need a report that can look at all the instances of Zipper 1 in Zipper FIELD1 and Zipper Field 2, and report on how many I need, separating these by color, depending on the main colour of the garment.

Hope that sounded right...I am just in the middle of this..

[zincb]

Your situation is a bit clearer, but now I have more questions:

I assume you have a separate field for "Color"?

It sounds like the user can enter into "Zipper Field 1", or into "Zipper Field 2", any of the following:

2 zipper7

2x zipper7

2 x zipper7

2 zipper Nr7

zipper7 x2

(2) zipper7

zweimal zipper7

:

Can you send me a file? I read German, so no need to translate.

[siou]

I do have a seperate section for COLORS.

I have conquered the problem of entering serials, which I had initially, when users entered all kinds of identifiers for this zipper such as your examples...2 zpper 7, zweimal zpper 7, etc.( this was in the first version I made...it was a mess.)

In the attached Trims Database, I have uniformed the way of creating the serial numbers for identifying this Zipper.

The user can only enter into FIELD NUMBER 1 or FIELD NUMBER 2 a single serial identifier for this ZIPPER2. (EX:ZADJ1, which means Zipper Adjustable 1).

The problem remains, When I want to make a report on the total amount of Zippers used for various orders, filemaker only wants to summarize the ZIPPER2 that was entered into FIELD1, and not add the instances when ZIPPER2 was entered into FIELD 2.

I need the USER to be able to choose for a report, after she does all of her invoices, a invoice number or group of invoices, and summarize all the instances in multiple fields that the ZIPPER2, for example was needed to produce the clothes to fill this order.

And, I really want to thank you so much for your assistance!!

YES, I can send you a file, and as I am initally from New York, It would be english.

Let me know where to send it.

[zincb]

A text file is attached. Please send to both places.

email.txt

[Sabrina]

I don't know if I'm too late to get in on this but if you have a summary of the first zipper field and a summary of the second zipper field, can't you make a calculation field that adds the two summary fields?

You both prolly have it figured out by now but that's what popped into my mind when I read your posts.

[zincb]

No, Sabrina, we did not figure it out. Maybe you can succeed where I could not. Perhaps an example can explain the situation:

ZipperFieldOne can contain one of several different types of zippers

ZipperFieldTwo can contain one of the same several different types of zippers

ZipperFieldThree can contain one of the same several different types of zippers

In record #1, you could have 1x GothZipA in ZipperFieldOne, 3x GothZipB in ZipperFieldTwo and 5x GothZipC in ZipperFieldThree

In record #2, you could have 2x GothZipC in ZipperFieldOne, 4x GothZipA in ZipperFieldTwo and 6x GothZipB in ZipperFieldThree

What is desired, is an output that tells the reader to order

5 GothZipA

9 GothZipB

7 GothZipC

To my thinking, to sum each type of zipper required for a multiple-record order would require an iterative, nested, looping script based on either all possible zipper types, or on all GothZip types entered on all records, for each ZipperField, keeping track of which ones have been tallied. To keep track of the sums for each zipper type would require a separate global for each zipper type (and advance knowledge how many zipper types are possible, plus the chance to add fields and do script mods every time that number changes).

I am sad to say that I threw my hands up in despair and suggested some fundamental data reorganization into logical data sets, rather than keep all this information in one file -- not what Ami Sioux wanted to hear, I am sure.

Perhaps you can help -- Good luck!

[siou]

I think I am coming to the same place as I think that there would need to be a script that tells the reports system to look in multiple fields to come up with the sum, but I am really new to scripting, and I am trying to quickly learn it to be able to finish this solution, but I am at my wits end, because it really wont work...

If you have any ideas. HELP??

Currently, the user has to choose what kind of a report they want, by performing a look up in one section of the report which sorts the fields by the exact lookup the person signifies.

EX: ZIPPERS 1 (USER CHOOSES) GothZipA, and then navigates to a report where all the instances of GothZipA in the field Zippers 1 occurs, gets that number and then has to go to also ZIPPERS 2, and do the same look up and navigate again to the Report for Zippers 2 and then add the two numbers together.

I need a report that will summarize all the instances that this zipper is used in multiple fields..but as i talk it out, i think i have an idea...

please if you have any ideas..i just keep going in circles..

ami

[Sabrina]

OK, what I am hearing (reading) is there are 3 fields where different zippers are entered. I am also hearing that there are infinite possibilities of zippers but most are already set. A main snafoo is to make the database be able to quickly adapt to a zipper added. Then there needs to be a script to tally each type of zipper entered in any of the 3 main zipper fields.

Question: are the 3 zipper fields on the same layout and in direct tab order of eachother?

Question: there was some discussion about color. Is the color in the main serial number that you are trying to track or does color need to be tracked also?

I do have an idea. I'll post it once I have a working model.

Sabrina

[Sabrina]

I came up with a working sample. Where can I send it?

Sabrina

[siou]

So, I thank you so much for your help Sabrina.

Yes, there are three different fields in my main database PRODUCTS where when the user makes a garment, she can choose to enter that the garment needs to use a ZIPPER 1 in the piece. The problem is that when you make a garment you can use up to three different zipppers. So, she may enter Zipper 1 into the First Zipper Field in the first garment, but in the next garment, she may enter the Zipper 1 as a secondary Zipper in this second garment.

I want to be able to make a report that after she makes all of her invoices, she can ask the system to look up all the instances of this zipper that she needs to complete the invoice when someone orders the two garments, and the zipper is placed in field 1 but also field 2.

The fields Zipper 1, Zipper 2, and Zipper 3 are in the PRODUCTS database in direct tab of each other.

So, color is a problem. I know. What would be marvelous is if the system could look up each instance of the zipper being used, and tie it into the color of the piece that it was made into, and that the report would summarize each of these instances by color as well.

And if you have any ideas.

My email is [email protected].

Thank you so so much.