Repeating fields & Value Lists from Related Files

[Andy Mohan]

I want to know how to enter data in repeating fields using value lists from a related file. In detail - I have two databases - one for invoices with repeated fields for Product Code, Batch No, Rate, Quantity etc. and another one as my Product Master with fields for Product Code, Batch No, Rate etc. and containing multiple records for Product Codes with different Batch Nos.

Now in the invoice file I have a value list for Product Code from the product file. When I select a particular product code the other fields (Batch No, Rate) automatically get filled up based on the lookup. I want that the repitition of the Batch No field should show only the batch Nos for the Product Code which I choose. At present I am getting either all the values or the values associated with the first product code. [color:"blue"] [color:"blue"]

[zincb]

It is not clear if an Invoice is permitted to contain multiple Products. I am unsure of the value of using repeating fields, which are "hard-coded" with the number of repeats, and therefore limit the number of batches you can invoice.

If only one Product is invoiced, consider using a portal to display the data.

If more than one Product can be invoiced, with all the associated Batches, you may want to consider importing a separate row in the Invoice file for each ProductID and BatchNumber.

Attached is a file that illustrates the two approaches.

invoice.zip

[nsuksta]

I have a question regarding using repeating fields and value lists. I am trying to set up a new patient intervention database, where some of the fields have pick one or more values from the value list. In my old patient intervention database, I was using one layout for each intervention. While that worked fine, it makes it very difficult to set up calculations and running reports from it, so I redesigned the database using just 7 layouts and repeating values in the layouts for each intervention visit. I used in the old database, fields with check boxes for those fields that had pick one or more possible values. I would like to use it in this one, but unfortunately, it makes it very hard to find more than two values (the repeating fields are arranged vertically) and I do not want to expand the field box as that would make each layout huge, pagewise, and some of the patients have up to 60 intervention visits so far. Is there anyway I can make the box drop down with the values as checkboxes or is there a way to make a portal to a location where the entry person could pick from a list and then those values selected are stored in the field? I am working on FM vers6.0. Any help or direction would be appreciated. Thanks.

[Oldfogey]

Repeating fields are a no-no. They are not supported well and cause nothing but trouble.

Use relationships with or without portals instead.

In both of these problems (Anand and nsuksta), define a second file to contain the data you currently have in repeating fields - one record per repeat.

[nsuksta]

Thanks for the help. I will have to figure this out then w/o repeating fields. Still very new to databases and FM Pro, but appreciate any help.

Nanette

[Andy Mohan]

Hi zincb,

I got your point. But let me explain the problem again briefly. What is happening is there is one field PRODUCT CODE and another BATCH NO, The BATCH NO field shows the related values based on th PRODUCT CODE. Now whatever PRODUCT CODE I choose at whichever repition, the BATCH NO shows the related values of only the PRODUCT CODE chosen in the first repition.

I hope I have made my point clearer

[jrb12]

Anand,

When retrieving data from a repeating field, you need to specify the repetition number otherwise you get the data from the first repetition. You may be using a construct that doesn't allow for a repetition number to be specified. Its speculation of course unless you post the field definitions, calculations, scripts and/or relationships. However, you are already familiar with relationships, why the insistence on maintaining the repeating fields?

In my experience the effort put into making a quirky repeating field solution work, and stay working, is about the same as reworking it to use another table. I've been through both scenarios this year and wish the former had not happened. The client, though he had insisted on keeping them, does too, now. Both because he can expand the database into new business functions much more easily and because his quarterly reports actually produce correct results.

Create an invoice items database. There is a good document on the FileMaker site to help you migrate repeating fields into a separate database.

Cheers,

John.