Repeating fields

[Emma]

Hello,

I am a newbie to FileMaker Pro, using the 5.0 version.

As far as I can understand it is advisable not to use repeating fields.

Why does the FilMker pro 5.0 template Purchase Orders use them?

I am trying to create a simple orderform, the repeated fields seems to work fine in the file as such except for when I am trying to perform searches and creating reports.

I have got three files. one product file ( with product ID), one Client file ( with Client ID) and one Order file (with OrderID)

Ideally, I would like the portal Ihave set up in the Client file to correctly display total productID with description for all the items purchased by a particular Client.

I would also like to create simple reports that list all items purchased in total, monthly yearly et.c.

My problem in creating the report is that (probably due to the repeating fields) is that it wont list the items in the repeating fields. The report calculates fine, the sums are right but I need it to display all the info.

Is there a way around this? I have been reading the FM manual backwards to front ...

There must be a really simple solution to this problem that I am unable to see.....

Is the FileMaker Pro 5.0 Bible good reading material ?

I would be extremely grateful if someone could help me!

Emma

[IdealData]

The use of repeating fields v. related fields needs to be considered on the characteristics of the problem in hand. There is a very strong push towards related fields for many good reasons, but repeating fields still have their place.

The "Purchase Order" form you discuss has probably been evolved from a time when related fields did not exist. I also have a "Purchasing" file for my company which is based on repeating fields - 'coz that's how it was made before FMP3. My longer term strategy is to convert to related fields for the following reasons:

1. An order will not be limited by number of lines.

2. I can produce multi-page orders

3. I can offer more text entry for any line

4. I can provide analysis at line level (presently only at order level)

5. Each line may have VAT applied, or not !

to mention but a few.

On the other hand I recently designed a report that needed a FIXED 28 days - perfect for a repeating field.

It's not always possible to spot the pros and cons of each solution without having experienced it beforehand - sometimes you've just got to go there.

Incidentally, if you attempt to report related items in a portal then you come up against the same brick wall you have with repeating fields as you have to define both types with a fixed length on your layout. With related fields you can define your report in the related file and execute by script from the main file. The report can then be a standard FMP report with header/sub-sum/body/footer style, and will be as long as necessary to include all the records.

Good luck.

[CobaltSky]

Mark's advice is sound.

Essentially, repeating fields are a hang-over from the early days of FileMaker. The reason they are still an option probably has mainly to do with the fact that there are a lot of people still using databases that include them.

Repeating fields are not flexible, and do not export or import very satisfactorily.

If you need to store several values in a single field, consider doing just that and separating them with a carriage return. Otherwise, I'd encourage you to use a relationship and a second file, as Marc suggests. On the whole, there is little that you can do with repeating fields that can't be done as well or better another way.

[RussBaker]

Repeating fields are usefull for storing database design features - like a global repeating field with 7 reps to store a different colour in each rep. Then, a calc like:

Day Background (container) = Get Repetition(g_Colour,Dayofweek(Date))

would place a different colour behind each day of the week in a list layout, for example.

also handy for storing little button icons too.

[harryk]

I think there are occasions a repeating field is prefered above going relational, though the relative poor support by FM using them (reports, getting data INTO repeating fields, sorting, finding) is a drawback.

I have a client database where I use repeating fields to store 52 weekly reports per client/record. (Weekly reports for pupils being absent, and some comment on that). How on earth to organize that relational, I wouldn't know. (Must confess I've a lot to learn on relational databases.)

Harryk

[CobaltSky]

I think RussBaker has pointed to a reasonable use of repeating fields - albeit one which aids the management of the database itself rather than the data.

However I should have said that storing 52 weekly reports per client/record is a compelling textbook case for the use of a relational structure.

In simple terms this would be achieved by creating a second file which is structured to hold one weekly report for one client per record. A ClientID field would be the only data stored in the second file which identifies the client. The main file would then require a relationship link to the 'reports' file which matches a clientID field in both files. (This can be set up using the 'Define Relationships' command on the 'File' menu).

A portal could then be placed on the main file and set to automatically display a scrolling summary of the available weekly reports for each client (and the database could be configured with some scripts so that a mouse click on one of the weekly records takes the user immediately to a detail layout with all the information about the report they clicked on).

Using an architecture such as this, various techniques could be used to:

1. Create and edit new reports from within the portal on the main file.

2. Store a number of details about a report (eg report date, report text, comments, category, summary etc) in separate fields (ie fields within a record within the related file), but access them as a single entity.

2. Filter and/or sort the display of client reports in the portal on the main file.

3. Search through all reports (from all clients or a single client) by keyword etc.

4. Produce a variety of sophisticated printouts of reports (eg all reports for a particular week).

5. Produce dynamic (ie with real-time on-screen updates) summaries of report data for each client (eg total reports per client, average submission time of reports per client etc etc) and across the whole client data set.

...plus a host of other things, many of which would be quite difficult if not impossible with a structure based on repeating fields.

[Rigsby]

Anyone who knows me from this forum knows my cry of

[harryk]

Cobaltsky, you took quite some time explaining the 'textbook' case of using a related file for the '52 reports case'..thanks for that, in fact it just pushes me round the corner of understanding the concept.

My original aim was, to create a database solution based on no relationships for reasons of, say, simplicity. This database is a client administration originally intended for (music) teachers, keeping track of the 'general' client information, the financial administration being able of calculating vat, totals, some quite ingenious way of booking the (mostly monthly) payments, and easily keeping track of amounts not being paid. Furtheron a library-administration, keeping tracks of materials borrowed (or is it lended..) to pupils, a profile section, with userdefined fields to store different information, and those weekly reports, first of all intented to keep track of absence, but also to weekly evaluate lessons. Furtheron of course printing of address labels, envelopes, and mail-merge functions (extended in the case of using it in Filemaker (lay-out accessible) and limited in the case of using it as a solution).

The database spans a YEAR period. January to december. For a new year, the client uses a new database, based on the old one. The client has to copy the database of the previous year, and then eliminates (all scripted of course) the (mainly financial) data not valid for the new year.

For this 'year-switch' I had the choice between 1) creating a new database and importing the nessecary data from the previous year or 2) copying the previous year and eliminating the data not valid anymore. I chose for the last option as somewhat more secure.

The only relational structure I use till now is an archive function. One copy of the database is intented as an archive to keep track of all the clients that are and that were. (so I can invite them to my farewell party after 25 years..)

To use the archive I make a copy of the most recent year passed, name it to-archive.fp5, and read in all new clients in the archive, and update address information etc. from the clients that are already in the archive. All automatically.

One of the problems I still have to solve: I want to make it all possible in a runtime solution, and I want the user to be able himself to switch to new years. Also I want older years accessible from the same runtime program. This involves I think some 'fake' relationships (#1 to #1 relationship) to the older files to be able to open them from the parent, and a consistent naming of all the files, for example the parent file (current year) always lap-main.fp5, and anticipating the relationships to lap-2002.fp5 lap-2003.fp5 and so on. (These are the older files in the future..). Then the user must be well instructed to keep them all together, and to be careful with the filenaming.

Though I have developer on board now, until so far I did not use it and not yet solved that part. I think theoretically my ideas about accessibility from within a runtime solution are correct.

Already without relational structures the way you suggest this setup is not quite simple, anticipating the future thought from a runtime solution. That's one of the reasons I avoided it till so far.. It will be a version 2 thing I think.

Any suggestions to my thinking aloud are welcome..

Harryk

[harryk]

>>Is there a way around this? I have been reading the FM manual backwards to front ...

I read mine the other way around...

In some cases you can go around the weakness of repeating fields in displaying/printing reports. Repeating fields are not flexible to print, you've got to start with the first field and there is no way to alter a column or row once it is set. Changing the field size (of the first field) sometimes gives unpredictable results in the width setting of the repeating fields to come, etc.

My suggestion is a bit theoretical, and only holds value if the number of repeating fields is, say, limited. And it involves some scripting.

This is my suggestion: create as much global fields as there are repeating fields, and put those global fields on your report lay-out (for printing) the way you want it. Create a script that walks through the found set, and that copies the data from the repeating fields into the global fields, and then print the record. It would look something like this:

go to record [first]

loop

set field[global1],[repeating field 1]

set field[global2],[repeating field 2]

and so on

if status[CurrentRecordNumber]=1

print[]

else

print[no dialogue]

endif

go to record[next, exit after last]

end loop

The chances this is useful to you are little, but in some cases, this idea might be useful.

The loop-repeated print-command causes every record to start on a new page this way? Think so yes. Wonder if there is a workaround for that, which I think there is not.

Harryk

[Rigsby]

Sorry Harryk