Calculate sum of specific values in repeatingField

[S Molly T]

I have a situation where I think the best solution would be to use a series of repeating fields. After searching/looking through many posts here, it seems that there is some sort of resistance towards using repeating fields.

I have two fields (actually about 10 but two will suffice for now) with 10 repetions each.

fieldA can have one of 20 different values

fieldB is the dollar amount associated with the value in fieldA

What I want to do is for each different value in fieldA have a calculation field with the running total(sum) from fieldB.

fieldA...........fieldB

rep1 bbb.....rep1 10

rep2 aaa.....rep2 20

rep3 ccc.....rep3 25

rep4 aaa.....rep4 20

rep5 bbb.....rep5 10

result1 sum of fieldA with 'bbb' is 20

result2 sum of fieldA with 'aaa' is 40

result3 sum of fieldA with 'ccc' is 25

...

result20 sum of fieldA with 'zzz' is 000

I would like to do this with a calculation field, but I do not know if it is possible. I can not even figure how to work it with a script.

FileMaker Version: 6

Platform: Mac OS X Panther

[Ugo DI LUCA]

Hi,

Here are a few scripted approaches. Note that it won't work in 7 because of the Repeat Value List bug

THT

FileMaker Version: 6

Platform: Mac OS 9

SummarizingRepeat.fp5.zip

[bruceR]

This is exactly why the "resistance" exists. Because you have to use clumsy limited workarounds to do something that is dirt simple with real related records. Look at some of the example files, learn how to do summary reports. You will be much better off.

[Ugo DI LUCA]

Just in case you proceed with Repeating data, the buttons in the quick test file I attached are not defined properly.

Please use the scriptmaker, or define the buttons to use the appropriate scripts.

Also delete the 2 first layouts and keep the last one as for the loop to work properly.

Was late, sorry

FileMaker Version: 6

Platform: Mac OS 9

[S Molly T]

Thanks for the help. Yes nice work thanks.

This is just part of a larger system that I have been tasked with building. I was really thinking that using repeating fields was my answer, but after reading here, I am not so longer so sure of that.

Current data input requirements:

Area1

- Adsize1...Adsize10

- Class1...Class10

- XL1...XL10

- IL1...IL10

- Gross1...Gross10

- Disc1...Disc10

- Net1...Net10

- AA1...AA10

- Pub1...Pub10

- Acct1...Acct10

Current requirements include five different Areas (Area1...Area5) with all the above fields, for a total of 500 fields. This does not include any contact info, billing info, payment details, scheduling details, or any summary/calculation fields.

Future requirements could possibly include 10 different Areas (Area1...Area10) with all the above fields for a total of 1000 fields.

Using repeating fields would significantly reduce the number of required fields.

Summaries will be required across all fields, but rarely (if ever) will they need to be sorted or searched through.

Not really sure if using a relational model would be applicable or even possible in this scenario. But being only a novice at this, I am not really sure.

If someone has or can point me to some example or other resources that may be similar, it would be greatly appreciated.

Thanks again for the repeating field summary help.

[bruceR]

It looks like you need 11 fields plus a few more, plus a proper relational design. It also sounds like you may need to get somebody with experience involved. You definitely do not need 500 or 1000 fields and that will become a complete reporting nightmare.

[S Molly T]

Yes exactly why I thought using repeating fields would be better than 500-1000 separate fields.

BruceR said:

. It also sounds like you may need to get somebody with experience involved.

Thats what you guys are here for, right?

Looking at the data input requirements from above, what would be the relationships and to what and how would the relationships work.

I could post the system that I have inherited and that I am building a replacement for if that would help.

FileMaker Version: 6

Platform: Mac OS X Panther

[Ender]

Looks like 2-3 tables, but I don't quite get the result sum fields. Can you give more about the way the data needs to be summarized? Do you have a screenshot of what it should look like?

[bruceR]

I say that because what you are doing now is like trying to eat an elephant through a straw. There is just WAY too much that needs to be discussed, it sounds like. Trying to get a full relational database education with a couple of lines a day of chat is not very productive.

[S Molly T]

Here are a few example questions that need to be answered with the summaries?

How many classAAA (stored in Class1...Class10) are there in Area1?

What is the total gross (Gross1...Gross10), discount(Disc1...Disc10), and net(Net1...Net10 of ads with adsizeBBB (stored in Adsize1...Adsize10) are there in Area2?

What is the total net (Net1...Net10) of all pubs with the value pubZZZ (Pub1...Pub10) in Area3?

No screenshots as of yet (except for the way it looks in the old system), pretty much all conceptual at this point.

[S Molly T]

While it may be true that my knowledge of Filemaker is limited, I do have a fairly good understanding of how relational databases work.

I just do not see how they would work for this particular situation.

All the data input requirements from above will be entered from a contract.

Each contract can have any number of Ads that will be required to be entered into the DB. One contract may have 6 different Ads, so there needs to be fields to enter AdSize1...Adsize6, Class1...Class6, XL1...XL6, and so on.

Almost every one of the fields can have any number of different values.

The Adsize1...Adsize10 fields can have one of over 20 different values.

The Pub1...Pub10 fields can have one of of 90 different values.

The Class1...Class10 fields can have one of over 600 different values.

The IL1...IL10 fields can have one of an indefinite different values.

And so on and so forth.

There really is no relationship between them except for maybe Adsize and Gross (If Adsize equals adsizeXXX then Gross would be 200). But even that is variable and changes between each Area.

If the user is entering all this information from a contract, what need is there for relationships.

[Ender]

I might be totally missing the mark, but can't you use a ten row portal with the fields you described (see below)? Then you can get the aggregate data however you want; with Sum() and Count() functions.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[Ugo DI LUCA]

Hi,

Although repeating fields sometimes have unexpected good flavours, I'd suggest again as Bruce to move from ANY repeating solution, which may or not include repeating fields.

A structure with Fields named Ad1, Ad2, wouldn't IMO lead you elsewhere. It's almost a derived perspective from Repeating fields but still relies on repetitions. Therefore VERY limitative.

This forum isn't intended to solve at the fly any development trouble you may cross. We're here to help you find some other angles of "attack" for problems you come accross.

But from tha above description, there's not much we can suggest apart what Ender is offering. I'm though sure there must be an easy way which wouldn't rely on this repeating "structure", if you might take some more minutes to explain in detail your goal.

FileMaker Version: 6

Platform: Mac OS 9

[S Molly T]

No, I think I am probably the one that is off the mark here. What you have is fine and would probably work, I just do not understand what the fields are a portal to . Unless you are suggesting that adsize, class, xl, il, gross, disc, net, aa, pub, and acct are all separate tables?

Thanks for the example, I am starting (I think) to get a better idea of what it is that needs to be done.

[S Molly T]

Thanks for all the replies (everyone). It is not my intention to use this forum to build the system for me or to solve all my problems. At least not yet, if it comes down to that then I will make the request in the appropriate forum.

I do appreciate the help, I just need a little nudging in the right direction (ok well maybe a big shove).

The ultimate goal is to allow for one point of data entry and multiple access points for information retrieval, reports, and summaries. I do not know what more detail I can give. I have 10 fields that could possibly have 10 repetitions. The fields are not related to each other or anything else and only have a common contract number. I am not any longer so sure of the last point there, maybe there is some relationship that I am not thought about, I have been studying on this for some time, I just do not know.

My sticking point is that if I have the above data entry requirements, I need to have fields to enter the data into, or is this assertion the basic flaw in my logic?

Again thanks for all the help, this forum has been a valuable resource for me for many years.

[Ender]

For a portal solution, the list of fields you have would be in a related table (related by contract# and area#.) In my earlier example, the Area# field is a Global. The relationship to the portal uses the Global as part of the relationship to the related table. See ER Diagram below.

[-Queue-]

Hey Ender, what program are you using for those ER diagrams?

[Ender]

I know, I know. I can't do any database work without drawing a diagram first.

It's ConceptDraw Pro. http://www.conceptdraw.com/en/

[-Queue-]

Cool, thanks!

[bruceR]

Hey Ender, what program are you using for those ER diagrams?

Another great app for this purpose is Omnigraffle, http://www.omnigroup.com/applications/omnigraffle/

[Ender]

OmniGraffle is nice because it comes free on some Macs, but I've found it harder to do ER diagrams in. It's great for flowcharting though.

[S Molly T]

Thanks for sticking with me till the end.

I think (with all the help) I may have sussed it out. As this discussion has sort of evolved away from the original intent, I should probably post my other questions in the appropriate forum (relationships).

Have a look at the new thread to see what you guys have helped me come up with.

Thanks again.