Filling in repeating fields with data

[The Mad Jammer]

I imported a bunch of data into a FMP file. I then ran a script to move data from the imported fields into various elements of a repeating field. I imported over 20,000 records, which only took a few seconds, but then it took about 18 hours to move the contents of 8 or 10 data fields into their corresponding element in the repeating field and set a corresponding value in another repeating field via the script. Wow, that was hateful and I don't wish to repeat the episode. The script I set up looked like this.

Set Field(Costfield, ImpField1)

Set Field(CostType, "Cost Field 1")

Set Field(Costfield -2, ImpField2)

Set Field(CostType -2, "Cost Field 2")

Set Field(Costfield -3, ImpField3)

Set Field(CostType -3, "Cost Field 3")

Set Field(Costfield -4, ImpField4)

Set Field(CostType -4, "Cost Field 4")

etc... with 8 or 10 fields being populated.

Why did this take so darned long to convert these 20,000 records? The import only tooks a few seconds.

What I am aiming to do is have two repeating fields, one with the Cost Category tied to a value list specfiying the cost categories, and the other repeating field would contain the associated cost of each category selected. This will give my user control over what is charged to each customer as well as the categories they wish to define. I would use these repating fields in a portal on another file to be updated.

I just can't believe it would take so long to populate the repeating fields.

[Ugo DI LUCA]

Hi,

If really your fieldnames are those you listed, with numeric tags corresponding to the repetition number, then a way around could have been to loop throuh each repetition on a layout in Form Mode, with each of your 2 repeating fields set with their corresponding tab order.

Show All Records

GoToRecord[First]

Loop

GoToField[CostField]

Loop

EndLoopIf[not Status(CurrentFieldName)="CostField"]

SetField[GetField("ImpField"&Status(CurrentRepetitionNumber))]

NextField

EndLoop

GoToField[CostType]

Loop

EndLoopIf[not Status(CurrentFieldName)="CostType"]

SetField[GetField("Cost Field " & Status(CurrentRepetitionNumber))]

NextField

EndLoop

GoToRecord[next-end after last]

I'm unsure why yours ended up to be this long, unless you weren't on a layout in Form Mode at the moment of the loop.

But it sure take more time than an import as you'll be looping into each of the 20,000 records and doing 16 SetField, which end up being 16*20,000 operations.

Also, when processing this number of SetField, it is good practice to take possession of the record before going into the SetField process. You'd perform this by a GoToField[ ] still in Form Mode and I'd recommand going into a calculation field or a serial number which you can't modify.

Now, the big point would be...Why would you use a repeating field in this case ?

You'd better split each rep into a separate record, into a eparate file, then use a reference to this file when looking to your value list process.

HTH

[Søren Dyhr]

Now, the big point would be...Why would you use a repeating field in this case ?

You'd better split each rep into a separate record, into a eparate file, then use a reference to this file when looking to your value list process.

Well I thought Queue and you have sworn an oath, never to ask a question like this about repeaters ...being humbly obedient to the brainstorming rules??

--sd

[Ugo DI LUCA]

Duh...

I would bet Queue wouldn't have answered otherwise here.

[Søren Dyhr]

I would bet Queue wouldn't have answered otherwise here.

My point exactly - you're brothers in arms! Afficionados won't raise the precautions flag in vain...

--sd

[The Mad Jammer]

Fellas,

I couldn't care less about oaths, afficianados or brothers-in-arms, so bag it.

I do care that the repeating fields seems to be anathema. I don't think it makes sense to build a separate file for each cost type. This would mean that every time my user wants to add a cost type I have to build them a new file and set up a relationship to the item that is being costed. At some point you could argue, let's build a separate file for every datafield and populate each file with the datafield and a reference and then define hundereds of relationships in a main file. That doesn't seem like a good idea in practicality and a new file and relationship must be built for every new field added to the database.

Since there is a cost type and an associated cost amount, it seemed reasonable to define two repeating fields that would have a direct relationship. The cost type is tied to a value list and could be selected in the future by the user. The information I'm importing already has these cost types defined and I need to identify the cost type for each cost amount. My plan is to delete the import fields after the repeating fields have been loaded since the data would be redundant at that point.

Frankly I could use separate data fields for each cost type and amount, but since there may be an arbitrary number of cost types, and I want my customer to be able to define new ones for themselves, and I don't want to have to drag 16 separate fields onto every P&L report that is generated or have to update every report layout whenever they add a new cost category, repeating fields looked like a decent solution. If the user needs more occurances of the cost categories to show up then one can simply change the number of occurences in the repaeating fields and the changes will propagate across the entire file, with very little required in the way of programming.

get it?

The Mad Jammer

[Ugo DI LUCA]

If the user needs more occurances of the cost categories to show up then one can simply change the number of occurences in the repaeating fields and the changes will propagate across the entire file, with very little required in the way of programming.

But it still needs your intervention at this stage, and this is bad stuff.

Actually, and this may answer Soren concerns too, I would never rely on a solution depending on a repeating field if I'm unsure that the number of reps decided at a start would be sufficient.

Also, in your case, these costs and stuff seems to be rather "variable" and this is also one of the things repeaters could be fight against, compared to a fluid relational design.

So, my guess is that you'd better define a new Table for the costs and do some categories there. Then, it will be even easier to update and use these values from there, and even easier to add costs and more stuff you need.

[Søren Dyhr]

I don't think it makes sense to build a separate file for each cost type. This would mean that every time my user wants to add a cost type I have to build them a new file and set up a relationship to the item that is being costed.

You'd better upgrade your understanding of relations by watching this video:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

...this is just the one and same relation instead of:

let's build a separate file for every datafield and populate each file with the datafield and a reference and then define hundereds of relationships in a main file.

--sd

[The Mad Jammer]

Fellas,

I've worked with databases for a long time and I understand relationships quite clearly. perhaps a small illustration will be more helpful. let's say I have a file named COSTS. It would look something like this

Cost Type Cost Amount

CostType1 $xxxxxx.xx (Cost1)

CostType2 $xxxxxx.xx (Cost2)

CostType3 $xxxxxx.xx (Cost3)

As you can see there are currently 6 fields on this file, 3 cost types and 3 cost amount fields. I can certainly set up a COSTTYPE file that has all the cost types defined and use that values from that file to populate the pop-up fields CostType1...CostType3. And it would have a refernece to the ITEM file so that we know what item is being costed.

Now let's import some data into the COST file from a text file. The data I'm importing is the actual dollar amounts and the fields Cost1, cost2 and Cost3 will hold this data and also a reference number to the ITEM record. So I import 20,000 records and populate the Cost1...Cost3 fields. At this point I have captured the dollar amounts and I know which ITEM they belong to. Now I have to use the script to set the corret values of CostType1, CostType2 and CostType3. Since I already know that Cost1, Cost2 and Cost3 have a static cost type associated with each of them, I can set the value of CostType1, CostType2, and CostType3 accordingly. So let's say that Cost1 is the cost of the item , and Cost2 is the shipping cost and Cost3 is the customs cost, for all the imported records. Therefore CostType1 would be set to "Item Cost' and CostType2 would be set to "Shipping" and CostType3 would be set to "Customs" for all the records that were imported.

Now comes the fun part. I have only put three costs on the layout. There are several more that need to be added to the file and in the future the user may want to put even more cost fields on the layout so that they can further granularize their cost structures. So instead of wonderful me coming and having to add a new field every time my client wants one, I thought about defining a repeating field for the cost amount that would be arbitrarily larger (contain more occurances) than what my client would normally use so that they can fill in up to, let's say, 15 costs per item. And the client could arrange the costs in any order as well. Meaning that for any new records added, CostType1 could be any of the for any

So instead of having to add a bunch of fields I could simply change the size of the repeating field, if need be. that would save my client some money in my time and save me time as well. As for using a file to populate a value list or simply using a hard coded list that the client can edit, I don't see much difference. Frankly, I think having a separate COSTTYPE file would be more overhead than a list of 12 or 15 cost types. Even if my user decided to have 25 different cost types, a hard-coded value list still seems to take up less space than a file.

What is also true is that there is a Charges file that would work in the same way. The client prints invoices based on the charges and there can be several categories of charges for each item. I have to import and convert that data as

well, coomplete with Charge Type and Amount. What I like about repeating fields is that they can be bigger (more occurances) than the user is likely to need for one item and the user wishes to have all of the items together on the invoice and truncate any blank spaces between the line items and the total.

I fully understand the concept of having the costs in their own file, but I don't think having a separate file for each cost type would make sense. Each file would then contain 3 fields, a reference number, a date and a cost amount. I can't imagine having 10 of these files just for the cost system when 10 fields are what is needed, each with 20,000 records initially and more to come. And then another 10 or 12 files for the charges system and then one file that referneces all of the cost fiels and one file that references all of the charges files. And if I did that the fields on the converged layout would be static because each cost field would belong to its own file and would have to be annotated accordingly.

[Ugo DI LUCA]

Hmm...

You said.

"Therefore CostType1 would be set to "Item Cost' and CostType2 would be set to "Shipping" and CostType3 would be set to "Customs" for all the records that were imported."

What you didn't tell us is how you are currently referencing these costs in your database.

Each of your items has a set of 3 to 15+ separate costs type associated, each with amounts that are regularly updated.

Apparently, from your initial description of the case, you're updating both the Cost types and Cost amounts when running the Import.

For this system to work, you are currently using *temporary* fields ('ImpfieldX' and 'Cost Type X') to receive and manage the imported data flaw. From their number, you determine which row of the repeaters would be used.

So right now, you need to expand either the number of repetition and create additional fields for the importing flaw when needed.

In substance, this means that your data structure is dependant of the Imported set.

Now, consider ONE file, that I'd call 'Product Parameters' with these fields :

serial

ItemRef (your ItemID)

Category (Charge or Cost or whatever)

Type (Item Cost, Shipping, Customs, etc.)

Amount

The big deal now is to think of a way to update each of these records, and this would depend mainly of the structure of the Import data.

But generally, when you can't update a file at once through an import, you'd either use a Parsing script that would populate each record accordingly, or you'd use a Temporary FileMaker file, which structure can meat your requirements.

So you'd need 2 additional files.

The 'Parameters' File and the 'ImportReceiver' file.

Set the 'ImportReceiver' file with those extra fields you were using in the Product file. You can even place 50 of each if you wish.

Basically, it should look like

Category (Charge or Cost or whatever) - that you'd set with a replace command when the import is run

ProductRef

TypeNum_1 To XX

TypeNum_Amount_1 To XX

As you can see, you can now easily synchronise the Parameters file from the 'ImportReceiver' file, either with custom imports or with a loop parsing and setting each Category, Type and Amount

Hope this make sense. Really, I wouldn't suffered that much sending repeaters to hell in this instance.

[Søren Dyhr]

I can't imagine having 10 of these files just for the cost system when 10 fields are what is needed, each with 20,000 records initially and more to come. And then another 10 or 12 files for the charges system and then one file that referneces all of the cost fiels and one file that references all of the charges files.

Ah!!! You didn't obviously find it nessersary to watch the video! As a hint should you go to the videos 8:30 minute ...and listen at the wise man when he tells his audience of the "Fair Share" - he's making the 10 fields be just the one in each a related record - pretty much as you imported them though you need the stripe them with what they go foreign keywise (a scripted replace) ....all it takes to adress say one price is to construct a compound key that singles out the particular record in the one related table/file ...or make a multiline to pluck a few related records for summing or whatever.

We're talking discounts based on purchase history and ordered quantities - aren't we?? Say it's factors instead then add the values Log(x)'ed and you have the product of all factors when 10^ the result of the sum over the portal.

--sd

[The Mad Jammer]

Ugo,

Thanks for the suggestion. I believe your solution can be simplified for this task although the file will contain about 80,000 records since there are about 4,on average, costs associated with each item. I may keep a separate file for Charges just for clarity's sake, although I understand your schema.

The import is a one time only deal. After the import hs been completed and the files setup the user would switch to using the FMP program full time. They currently are using an old DOS database program that I wrote in Clarion back in 1991. It still works great and they use it every day, but obviously it needs to be updated to a newer operating environment have some flexiblilty added to it. In the old program there were no Cost Type fields because that version of Clarion didn't support dropdown boxes. Cost Types were simply field lables that weren't stored on the database, but displyed on the screen as text.

In any case, I guess I can have the Cost data and Charges data displayed and updated in separate portals on the Item screen. That way the user has a way of seeing all of the costs/charges associated with the item and can update these costs/charges at any time. This will be a nice solution for the user.

This changes the import methodology somewhat though since I'll have to split up the cost fields into separate records, but that is easy.

Thnaks for your help on this.

SD, we're not talking about discounts or anything like that. We're just keeping track of the costs/charges associated with an item. I have to tell you though that I didn't understand part of your message at all. You said

"pretty much as you imported them though you need the stripe them with what they go foreign keywise (a scripted replace) ...."

I have no idea what that means. That sentence makes no sense at all. What is "stripe". Usually thats what separates lanes on a road or what zebras wear. Just a matter of trying to understand what you are saying. It would be nice if you weren't so condescending though.

[Søren Dyhr]

It would be nice if you weren't so condescending though.

Sorry 'bout that ...Wiio's law says: "if a message can be understood in different ways, it will be understood in just that way which does the most harm"

--sd

[The Mad Jammer]

Perhaps it was understood in the way it was meant, perhaps not. In any case, proper communication is the burden of he transmitter, not the receiver, for only the transmitter knows what they really mean.

The Mad Jammer

[Søren Dyhr]

Lets reverse the burden a bit ...why are you so keen en repeaters?? - They are obviously breaking 1NF!!!

[The Mad Jammer]

I'm not that keen on them. They just happened to be the first thing that I thought of to solve the problem given the structure of the data i ahd to work with. In fact, after this discussion I'm wondering what repeating fields are used for? Is there a preferred usage of repeating fields or are they basically useless?

Part of my background is in COBOL mainframe programming so I'm quite familiar with repeating fields and they serve their purpose well in this enviorment. Our shop also uses an ADABAS mainframe database. This database is an inverted list database and uses repeating fields (occurances) to store like data, like the names of principals and assistant principals at a school as well as independent data fields. Me and a couple of others started doing psuedo ADABAS relational databases here several years ago like breaking pieces of student data into separate files related by the student ID. Finally a few years ago, a few of us were able to get going on MS-SQL, Paradox, DBIII, FoxPro etc.

In the end I have a lot of experience with a lot of different types of data storage. I wasn't that keen on the repeater but simply got locked in that paradigm. The ensuing discussion changed that for me and I'm quite glad it did. It just took awhile to see the jump from where I was to where you and Ugo were suggesting this should go.

The Mad Jammer

[Ugo DI LUCA]

I'm very glad to announce, me, the repeater fond, did finally actively participated in turning someone away from them...

[The Mad Jammer]

Ugo,

Glad to know that we both won on this one.

The Mad Jammer

[Søren Dyhr]

after this discussion I'm wondering what repeating fields are used for? Is there a preferred usage of repeating fields or are they basically useless?

Read this thread:

http://www.fmforums.com/threads/showflat...true#Post117420

They are FAQ's and I'm with Bruce on this one:

They are, almost always, the wrong choice, especially for novices, and they do in fact prevent people from learning basic relational design as can been seen by looking at about 90% of the questions here about repeating fields including this one

But read the entire thread because Queue have his points allright ...but your splitting of imports taking so long to process, made have made you think why're people using filemaker in the first place?? Interpolating from other tolls might either hit it right on the nail or flunk - depending on the inner logics of the tool in question.

Filemaker does actually differ considerably by having no native triggers, but instead calcfields as in spreadsheets and validations on calculations instead of scripted. I practice a rule, which might prove wrong - but I never use repeaters for anything the user can touch alter directly etc. but instead as an efficient measure to calculate compoundkeys and repair silly limitations when IWP'ing such as the 5row issue for list layouts.

--sd

[The Mad Jammer]

I know that repeating fields can be useful but not as a substitute for relational database design, which is actually what I started out doing in this thread. However the concept of one field that has a finite number of occurances in it can simplify the design of a layout and a file. Queue's points are well taken and my experience with mainframe repeating fields also holds that they are useful. In essence they are nothing but one dimesion arrays and are easier to interrogate in some languages (think VB, Foxpro) etc. than in having to write code that interrogates every single field. Certainly a piece of code like the following

--------------------------------------------

Dim x as long

Dim xArray(20)

For x = lbound(xArray) to Ubound(zArray)

If xArray(x) then

...(some code here)

End-if

Next x

--------------------------------------------

is easier to write, consumes less time and is every bit as effective as writing 20 statments like this

Dim xArray(20)

If xArray(0) then

... (some code here)

End-If

If xArray(1) then

... (some code here)

End-If

etc...

In earlier versions of VB it was easy to place an object like a text box or label on a form and then simply copy and paste the object several times to set up an array of textboxes, all of which were accessed by their index number.

This is where repeating fields really come in handy, particularly if you have to enforce validation rules on all of the elements of the array.

So this is where my perception of repeating fields comes from. Also, I don't think that FMP would have included them if they had no use or would interfere with the correct construction of an application. As such it is a tool and every tool has its own use. Knowing when to use the tool is the important part.

Jammer

[The Mad Jammer]

I also find it interesting that so much time, emotion and energy has been devoted to the reapeating fields, are they good or bad, subject

Jammer

[Søren Dyhr]

Also, I don't think that FMP would have included them if they had no use or would interfere with the correct construction of an application.

Prehistoric Filemaker weren't relational but had repeaters, they've stayed ...since dreams of a conversion algorithm towards real relations is too far fetched a wish because the utilization not in every case is straight forward. So we can only guess why this backward compatibility is keept 9 years after filemaker went relational.

But there is something of Queue's reasoning that goes like "Let the kids go to the beach unatended, some will certainly learn to swim ...to heck with the rest"

--sd

[Ugo DI LUCA]

Hi,

Curiously enough, I started re-using repeating fields in my solution with FileMaker 6, while I had quite stopped with them since FileMaker 3. Not to mention that my FM2 solutions were filled with them at a point that I wouldn't even now kno how to build such a solution.

While we all decided once that they should stay locked for ever in the Interface jails, I find it rather interresting that FMI brought us some new tools for them with FileMaker 7.

They now gained a "release on probation" status, and are even allowed some relationships outside the jailhouse, without any chains nor balls.

Even with the new features 7 brought just for them, they still are far less attractive than a real Relational Model, specially compared to the new one, and I'd still not rely on them to store variables data into the relational architecture.

However, as you mentionned it, their role as arrays could be a real interest and I believe the few following words, from Ray Cologon, extracted from one of his brilliant suggestion, while 7 wasn't out yet (buy may be he knew at that date), could be of interest.

"Ugo's suggestion that "you'd never catch more than the first repetition if your repeating field is referenced through a relationship." is, as he himself predIcts, totally wrong. The above formulations will work just as effectively wherever the source data is located.

And as for Lee's suggestion about repeating fields, that "such as they are, they're as good as they have ever been or will ever get....", I'm afraid I couldn't agree with that either. In fact, quite the contrary.

Repeating fields are the closest Filemaker has thus far come to an array architecture. In early versions of FM, this was provided (and most frequently used) as a poor substitute for relationships, and since relational structures were offered in version 3, that application of repeating fields has been less desirable.

In their current implementation, because of problems like the one that is the subject of this thread, repeating fields really don't cater for arrays any better then they DId as a stand-in for relationships - ie they remain clunky. However array architectures have value in their own right, and most advanced dbms systems offer support for arrays and variables in some form. Thus as FMP matures I would be *quite* surprised if repeating fields were not only retained, but enhanced so that they provide a more adequate support for the handling of data arrays.

In fact I would not be at all surprised if we see adDItional functionality or improved handling of repeating fields in an upcoming version of FMP, as it is one of several logical 'next steps' in the evolution of a truly industrial strength database development environment."

My newest FM7 solution does use repeating fields, not only for Interface purpose even though this still is their main purpose.

I trust their speed in my Statistical Module, as even with 7, Sum( ), Average ( ) and other calcs are slower involving relationships, and that in this case, we're just working real arrays.

One or Two years ago, some where ironising on me suggesting SelfJoins and relationships everywhere in my answers on this Forum. Then, they felt as if I was one of the last of the latest users of repeaters, with Queue and Goran Djukic -aka Dj -

Well, may be they pushed me into this incredible mix but it was a very good thing to re-discover repeaters after forgetting them.

I even liked to play again with my old FM2.1 solution (still available to download as a Trial version by the way for those interrested, working smooth on OSX) some months ago.

[Jerry]

Just wanted to say thanks for including your script with your post. I've been wanting to do almost the exact same operation for a while, & couldn't figure out where to start. Just had to change the field names and add the loop & next record statements. My database is much smaller so it only took a few seconds to run. I'm not getting into the discussion on repeating fields; mine are doing what I want them to do where I want them to do it, so I'm happy.