Hello,

It's me, Vandy the MS Access transfer student again. I have a question about repeating fields. I have a purchase order where you can list multiple items from different suppliers, easy enough. In Access I would make a subform related to the parent form, but here I see you can set a field to 'repeat'. This is totally cool and cuts down a lot of work. But the problem I'm running in to is that I want to make an additional layout where I can find a vendor and list all the items from different dates... I can't figure how to do this.

I tried to go to the Find and put in "Walmart" or some company, but it returns all the items in the repeated field from that particular order, not just "Walmart". So how can I do this? Or can I not use the repeating form in this manner?

Thanks again,

Vandy, mayor of Newbieville

Hi,

You can't at least in the simple way.

Take a look to the RelVsReps sampler here to see how to take advantage of relationships rather than repeating fields.

You need to get rid of the repeating fields and move to a proper relational design with a portal. There are plenty of examples that come with your Filemaker install. You can't individually find, delete, sort, report, etc. with repeats. Get rid of them.

Repeating fields are a legacy from FileMaker's non-relational past (pre-version 3). Experienced developers today use repeats only for utility functions, e.g. storage of graphics, never for actual data. (And with version 7's unlimited tables there's even less reason to go there.) They are generally to be avoided, or you'll end up doing more work, not less.

Just set up a related table for your line items, it's simple once you get the hang of it.

never for actual data

Never for actual static data. For dynamic or calculated data there is often no more efficient or cleaner solution than calculated repetitions or self-related calculated repetitions. And for large 'spreadsheet' layouts, there is no faster calculating solution without using a plug-in. Once the related repetitions bug is fixed in version 7, with its 32,000 repetition limit and new individual summarizing capabilities, the possible uses will be even greater. They are definitely not just a legacy with no good purpose anymore or only intended for utility purposes. We've only seen the beginning of what they can do.

Now, if they would only make Set Field dynamic for reps, the possibilities would be almost limitless.

You can set field for a repeat with applescript or with standard features of FM7 but not with an explicit script statement. What related-repetitions bug are you referring to?

Right, what I meant is that they must be hard-coded instead of, say, repetition by field or calculation. I usually set them using a loop on their own solitary layout and use Go to Field [field] then Set Field [ , calc] so that it doesn't have to be hard-coded, but it would be really nice if such a workaround wasn't necessary. AppleScript is not an option on a Windows-only network.

The bug is that if you reference a field that contains repetitions, like in a value list, if you don't limit the relationship, it will use all repetitions for all records in the given table. However, if you specify to only use records starting from a particular relationship, only the first repetition for the related records will be used. See attachment for an example from a solution that works in 5.5 and broke with 7.

96712-vltest.zip

Thanks for the response. I think I'll shoot down the repeating fields.

Okay, I get that I have to make it relational, that is, the order table must relate to a table that contains the line items, but how do i do this and then make a layout with a subform?

I made the table and it's relational by work order number, but it's only one-to-one, one work order with one item. How do I make it so you can put multiple items in without having to make 8 separate fields for items?

Use a portal. Each line in a portal displays one related record. Set your relationship that the portal uses to 'Allow creation of related records' in the child table.