Populating field in related records

[bluearrow]

Hi,

What do you think is the best way to populate a field/fields in all related records?

The options I spot are:

1.- Go to related records > Create 2 variables, one holding number 1, the other counting the number of related records > Use this variables in a loop to populate the records

2.- Go to a layout showing related records > Replace the value in the field for all records.

I do not like either one. The first one is too slow and too cumbersome in some cases (many fields or many records). The second one requires a specific layout and I particularly do not like to use the replace command at all. It can easily lock if one of the related records is in use, and is even slower than the other option.

Is there another imaginative, faster solution?

Thanks

[Inky Phil]

Hang on while I pull my imaginative hat on.....there she goes and ain't she a beauty!

Anyways, if the number is common to all related records (as it sounds from your description) why not just store it once in the parent record and then just display it via the relationship?

Phil

ps Sorry if this is a bad suggestion but putting this hat on gets me into all kinds of trouble. For one thing I imagine that I know what I am talking about!

[bluearrow]

...if the number is common to all related records (as it sounds from your description) why not just store it once in the parent record and then just display it via the relationship?

I cannot just display it. It is a quoting/invoicing system. The related info can be, for instance, the price of an item, which of course may change, but you do not want it to change in the quote or the invoice. Also, I need to copy the quote invoice ID to the quote line items, so that the line items know the quote and the invoice they belong to. This are just examples. There are many other fields that need to be copied in different steps of the process.

[Inky Phil]

Ok, I understand now that you might need to store a price at any given point in time but it still seems a little odd that you are storing the same information in more than one record.

You have said that the related info might be the price of a line item whose price might change but whose price you do not want to change on the invoice or quote. Better surely then to store the details, line items and their prices relevant at the time of the quote, in one record and the other details of the line items (that do not change) in another record.

To do it your way would be to have an items table where you are going to store not only the current price but also any price ever quoted or invoiced plus the id's of all the quotes and invoices.

Once again forgive me if I am misunderstanding something here but I feel that perhaps there is a better structure to be had here

HTH

Phil

[bluearrow]

Ok, I understand now that you might need to store a price at any given point in time but it still seems a little odd that you are storing the same information in more than one record.

Yes, I agree. That is the reason I wanted to see if someone has an alternative.

You have said that the related info might be the price of a line item whose price might change but whose price you do not want to change on the invoice or quote. Better surely then to store the details, line items and their prices relevant at the time of the quote, in one record and the other details of the line items (that do not change) in another record.

Sorry, but I do not understand. The price field was just an example. Does not need to be the price. Anyway, I could create a lookup or calculated field, then lookup or calculate the price at the moment of creating the line. But I do not like to use lookups or calculated fields. They could be a cause of trouble in the future (importing records, for instance, or when solving user errors). Do not need to be, but usually they are a danger. I prefer to keep my database completely free of lookups and calculated fields.

To do it your way would be to have an items table where you are going to store not only the current price but also any price ever quoted or invoiced plus the id's of all the quotes and invoices.

This seems like a little bit crazy. In a year I could have hundred of thousands line items. If I add up every price ever quoted it will easily go over millions of records. In the case of price (and like I said, price is just an example) I store the client's reference rates in one table. When I create the line item, I let the user decide which price to use, and change it in the quote, should they need to do so. No need to store every quoted price.

[comment]

Looping between records should be, in most cases, fast enough. If it's not, there could be something wrong with your implementation. Certainly, there's no need for "2 variables, one holding number 1, the other counting the number of related records". Filemaker has a built-in mechanism for looping through the found set.

If the success of the operation is critical, you could check each record for being open or not, and if necessary return to it later. This is one reason why calculations and auto-enters are preferable, and scripts that operate directly on core data should be only used as the last resort.