Repeating Field Alternatives

[Calantha]

Any suggestions on alternatives for repeating fields?

I use repeating fields for contact histories, as well as a history of when a newsletter was sent and which newsletter was sent.

However, I would like these to be searchable, as well, I would like to eventually use individual repetions for summaries.

So if I need to make multiple entires, for example

this date - inquiry

this date - response

this date - info pack

this date - response

How could I do so without using repeating fields, especially if the order is always changing so I can't just have an inquiry field, response field, info pack field, because it changes for every individual.

Any ideas?

Edited October 6, 2005 by Guest

[Ender]

Create a relationship to a second file that has the contact history fields, and use a portal to show the related records.

Check the manual on the basics on setting up a relationship and a portal.

[Calantha]

I'm confused as the benefit. It seems more work for me to have to enter the history in another file.

Edited October 7, 2005 by Guest

[Ender]

Using a portal is like using a repeating field in that it fits on your current layout and there are multiple rows to fill in. But there are several benefits, things that repeating fields can't do:

1. portals can show multiple fields from the related file.

2. portals can be sorted by one of those related fields.

3. portals are expandable and scrollable; they hold as many related records as needed.

Also, by separating the repeating data into a second file, it becomes very simple to do aggregate things with that data, like summary reports and finds. It is also very easy to show or aggregate subsets or larger sets of the data.

For example you might want to see all the Contacts that received an "info pack" on a given date. This could be done with a simple search and summary report in the History file, or you could use filtered relationships to show a portal containing these contact histories.

[krdzine1]

Similar question... I'm trying to create a product database and each item has a Part #. Some items also have up to 10 Color options and 10 Size options. The old database used a single field to identify each Item + Size + Color item as seperate items. This creates problems when publising online or creating a print catalog. I made a "Final Part #s" field that is a calculation of the 3 fields...Part# & Size & Color. When the list is generated only the first in the repeating field is right. The rest ignore the Part# field entry....and so on. Does this make sense? How can I fix it so a single record can accurately reflect all of the size & color options for each product?

[Ender]

That could be corrected by using the Extend() function on the non-repeating parts in the repeating calc. However, you would do well to abandon the repeating fields and use a portal of related values instead.

[krdzine1]

So create a seperate table that would contain that information... how do I structure that? Just the Part#, Size and Color Fields in that Table...? Bear with me a little... I'm a quick study but database creation only a tiny part of my job.

[Ender]

As I told Calantha, the basics of setting up a relationship and a portal are covered in the manual. For a more detailed explanation of relationships in FileMaker, you might check out the "White Paper for FMP Novices" from Codemasters Workshop:

http://www.codemastersworkshop.com/downloads.html

There are also plenty of books on FileMaker, though you'd want to find one that covers FM5 or 6 rather than FM7 as much is different between them. Check out the FileMaker Resources forum for suggestions.

[krdzine1]

My question is really about how to design the structure of the portal more than what is a portal I guess. The goal is to have FM do the work of generating final product codes. If you are suggesting using a portal to enter each size color combination as a record in the portal for each product... that's counter productive for what I'm trying to accomplish. I'm looking for a little bit more specific help. Any ideas are helpful. Thanks.

[krdzine1]

The extend function was helpful but only partly... it only extends to match the next variable. So if I have 5 colors but 10 sizes the code only extends to the first variable.

Part#:G234300

Color Codes: -3, -1, -4, -5

Sizes:06, 07, 08, 09, 10

First few results returned.

G234300-306, G234300-107, G234300-408, G234300-509, G23430010

Would I be better off using non-repeating fields for this? Where I'd have Color1, Color2, Color3, Color4, Color5, Size1, Size2, Size3, etc. I'd then have to have a FinalPart1, FinalPart2, FinalPart3, etc. too wouldn't I? It seems like it should work without it but...

Edited October 12, 2005 by Guest

[Ender]

So for n colors and m sizes, you need to generate n * m product codes? What's the purpose of these product codes? Will they ever be used later in relationships, like for linking invoice line items to inventory? If so, then a portal of avaliable size/color combinations should be used. If it's only for display purposes, then a simple multi-line text field might be easiest.

Whether you use a portal, a repeating field, or a multi-line text field for holding the product codes, they can easily be generated by a script using nested loops, provided you have the color and size options in a consistent format that can be parsed.

For example, say you have a Color Options field and a Size Options field, with the respective codes separated by commas:

Color Codes: -3, -1, -4, -5

Sizes: 06, 07, 08, 09, 10

With that, you can use one loop to scan through each of the color options, and within that have another loop scan through each of the sizes. For each size/color combination, you can either add a new record in the portal or just append the product/size/color to a text field, resulting in something like:

G234300-306

G234300-307

G234300-308

G234300-309

G234300-310

G234300-106

G234300-107

G234300-108

G234300-109

G234300-110

G234300-406

...

[krdzine1]

That's exactly the result(s) I am looking for. In answer to the first question this data primarily used for generating a catalog... inventory is not a necessary aspect on my end but having the ability to use these results is. In our catalog we are more likely to generate a color/size matrix for each item but this allows us the ability to generate final part #s too.

I've never created a looping function/script can you post a simple example on how to construct one?

HISTORY: In the past each item (part+color+size) was entered as a seperate record with a product name, descriptive text and price. When we'd export the list to put into the catalog we'd have 5000 items when there are really 250 items with various color and size options. We'd then have to parse out the repeated data manually to format our pages. A major waste of time.

[Ender]

I'll give you the rough idea, but you may need to fine tune it.

In addition to the Part, Size and Color fields we talked about before, it will help to use a few globals to keep track of where we are in the loops:

gCountColors

gCurrentColor

gCountSizes

gCurrentSize

So the script would be something like:

#Initialize the loop control variables first

Set Field [ gCountColors; patterncount(Color Options, ",",1,1) + 1) ]

Set Field [ gCountSizes; patterncount(Size Options, ",",1,1) + 1) ]

Set Field [ gCurrentSize; 1 ]

Loop

  #Loop through sizes

  Set Field [ gCurrentColor; 1 ]

  Loop

    #Loop through colors

    Set Field [ ProductSizeColor ; ProductSizeColor & "¶" & ProductCode & middlewords(substitute(Size Options,",", " "), gCurrentSize, 1) & middlewords(substitute(Color Options,",", " "), gCurrentColor, 1) ]

    Exit Loop If [ gCurrentColor >= gCountColors ] 

  End Loop

  Exit Loop If [ gCurrentSize >= gCountSizes ]

  Set Field [ gCurrentSize; gCurrentSize + 1 ]

End Loop

You should know what's going on in case you have to troubleshoot it:

The outer loop goes through the sizes and the inner loop goes through the colors for each size (these could be swapped around if you wanted the result listed differently.)

What we are doing is traversing through each comma-separated string of colors and sizes. I use that Set Field[] in the middle to both select the correct size/color and to append it to the string:

Set Field [ ProductSizeColor ; ProductSizeColor & "¶" & ProductCode & middlewords(substitute(Size Options,",", " "), gCurrentSize, 1) & middlewords(substitute(Color Options,",", " "), gCurrentColor, 1) ]

The substitute changes any commas to spaces, so that the commas will be removed in the result and the middleswords() function will correctly pull the right size or color, should a space be omitted in the string.

[krdzine1]

OK. I swear I'm not this stupid.

I've created the 4 global fields you mentioned. Right now they are just text... should they be calculations or something else? I also created fields that match ProductCode, SizeCode, ColorCode and ProductSizeColor to be closer to what you've shown.

Where do I create your script... Scriptmaker won't let me structure it the way your shows... and as a calculation it says the table "Set Field" doesn't exist. I'm obviously missing something.

I appreciate your patience.

[Ender]

That was a script, so yes, you'd use ScriptMaker.

The globals should be global numbers, since they deal exclusively with number functionality.

Though my script is a little rough, it looks pretty much as it should when the steps are selected and filled in with the proper target fields and calculations (ignore the extra parenthesis at the end of my Set Fields.)

[-Queue-]

Set Field is a script step. Select it from the list of steps on the left. The first parameter (before the first semicolon) is the target field to specify. What follows after the first semicolon is the calculation to specify (minus the extra parentheses, as Ender has already indicated).