Migrating from Repeating Fields to . . .

[Wickerman]

Hi --

I'm performing a re-design of a flat (1 table) cataloguing database that employed repeating fields in a number of places to handle multiple values. I'm trying to keep the structure as simple as possible because this is something given out virtually for free to small organizations that can't afford a lot of maintenance.

I want to get rid of the repeating fields since they're a legacy of the pre-relational FMP days and are generally seen as a Bad Idea. It seems my two options are to (a) make the field a simple text box and instruct users to use a carriage return between entries, or ( create child tables for all these fields.

Solution (a) if course can lead to shoddy data entry -- but solution ( adds considerable complexity and, for instance, makes report layouts a lot more complicated and is just overall more work to implement and perhaps, maintain in the future.

Is there a sense of a best approach to this, or some third option I'm not seeing? Also, with either solution there will be some import issues which seem like a pain -- I'm supposing I have to create some sorts of looping scripts to slot the repeating field data into the new structure, but not sure how to go about it -- do I import the fields as is into the new design and then go at it, or . . . ?

thanks for any pointers!

Albert

[LaRetta]

Hi Albert,

but solution ( adds considerable complexity and, for instance, makes report layouts a lot more complicated and is just overall more work to implement and perhaps, maintain in the future.

On the contrary ... having records instead of multi-line is much easier - particularly when reporting and maintaining as your needs change. It is easier overall.

Are you aware that, when you import, you can break repeating fields out into records?

[Wickerman]

Ah -- I had forgotten about that option of splitting up repeating fields on import. That helps a lot, obviously . . .

THe reporting issue has me a little uneasy. My impression, from an early question I posted, is that in order to have say, multiple child entries listed neatly in a single-page print-out of a parent record, I wind up having to script some sort of concatenating field -- unless I'm using the context of the Child table. But if the parent table has multiple child tables, what context do I use? Don't I have to put portals on the report layout from the Parent context, which winds up making for awkward layouts? Or else create print-out fields that run a script scooping up the child data and putting it in a printer-friendly format? This is a basic issue I haven't figured out yet and just feels daunting, and complex . . . .

Albert

[LaRetta]

Hi Albert,

It would be wise at this point, then, to provide us with a basic file and let us assist you in the structure. But I can assure you that, under 99.99% of the circumstances, using a child table is far superior and I doubt that this is an exception! If there is an instance where it would not work for you then there may be other approaches IN CONJUNCTION with a properly related child table.

But I will not advise without more information. I will only encourage you to let some of the relationship experts here walk you through a proper structure now. It is much easier to do it right to begin with (as you know). :wink2:

[Wickerman]

Okay LaRetta -- that's very generous of you to offer having a look.

I've never posted a file here before and find that .fp7 extension isn't allowed for uploads? I suppose I have to compress the file -- I have stuffit expander but that only seems to unpack -- is there a free Mac utility for this? Or other approach . . .?

Albert

[LaRetta]

that's very generous of you to offer having a look.

I am hoping OTHERS here have a look. I am not a relational master and, to determine a good base structure, it usually takes a few minds together.

Unfortunately, I do not know how to zip a file on a Mac. Maybe others can speak up but yes, you will have to zip it first. And please tell us which post (or prior situation) you ran into that was difficult to generate from a child table (or was it because you had the data in repeating fields at the time)?

Regardless, FM Forums can help you set your new structure up properly. :smile2:

[Wickerman]

Oh, duh -- the zipper is built right into the OSX finder. So here it is. I hope someone can have a look. In the other thread I don't think I spelled out the scenario any more fully than I have here -- I think it was in the context of another discussion.

So, I'm posting here a demo file with a single sample record. I've set it to Guest with full privileges and no password. I hope someone can have a look.

The file should open with the 'Keywording' tab forward. You'll see that I've got two simple Child tables for GENRE and FORM, and I've entered a few dummy values. Also, there is a more elaborate KEYWORD field which is a Child Join Field connected to a controlled vocabulary kept in the Lexicon Table. So I've entered several dummy values there too.

If you hit the PRINT button at lower left there will be a truncated version of my print-out version of the record, and at the bottom I've indicated what I hope to display, which is the terms enered in these three fields. Ideally, I'd like to know how to display these values in either a column form (I entry per line) and also a more efficient line form (some records could have dozens of keywordtags, so perhaps I want to display them separated by commas?

This doesn't seem as though it would be a very uncommon need, is it? I'm puzzled why I haven't really been able to find anything like it demonstrated in all the books I've bought . . .

Thanks for the help!

Albert

CatalogueDemo.fp7.zip

[LaRetta]

Hi Albert, Your structure doesn't look too bad to me!

I wind up having to script some sort of concatenating field -- unless I'm using the context of the Child table.

You don't have to script a concatenating field - you can use a calculation. Although I don't totally understand your file (see revised version), I understand your situation. You want to print everything about your parent record (all child records, whether Genre, Keywords (Lexicon), Forms etc on one Form.

Look at your print out now (Print List) and then look at the calculation fields referenced within the merge (and the Lexicon calculation which is referenced as well). You will indeed need to group your children. Since both Genre and Forms are single words, you can use commas between them. As for Lexicon, I would leave it as a list so you need to use a calculation in that table and then refer to it.

But overall you're looking pretty good, I think. Go forth and prosper.

One weakness of FileMaker is an inability to create outer joins. Until that is resolved, we need a few workarounds for it. But your structure seems okay and that was my big concern.

CatalogueDemoREV.zip

[LaRetta]

BTW, I see not a single repeating field in your file. I thought we were discussing eliminating repeating fields and that is what I was attempting to talk you INTO - ditching the repeaters. :clap:

[Wickerman]

Ahhhhh . THANK YOU THANK YOU THANK YOU !

This was *exactly* what I was looking for and I'm delighted it's so relatively simple. I wasn't familiar with the use of Substitute / List functions, so this will bear fruit for me in all sorts of places.

...which takes me back to a recurring feeling I have that there is a very significant *hole* in the otherwise excellent universe of FMP training materials: a workbook that takes a developer through all of the most useful functions in a way that illustrates their most common uses and makes them familiar to the student through repetition. The 'piecemeal' approach I'm bumbling through is working for me fairly well, thanks to helpful support like yours, LaRetta, but a good training text for calcs and scripts building mastery of tools from the ground up would be invaluable. (The former teacher coming out in me).

Yep -- I had no doubt the repeating fields had to go . . . but I wasn't quite sure how to handle this piece. So thanks *very* much!

albert