Script step to "Set Field" for all found records

[Agent Ska]

I'm looking for the most efficient way to update a field for all found records.

For example, I have a field called orderStatus. I already have a script that searches for all records whose orderStatus = "New".

Now, I want a script step that sets the field for all of the currently found records to "In Production."

I understand how to set the current records status, but how do I set them all?

[Agent Ska]

Yeah, pretend I didn't take my stupid pills and ignore this post. I was trying to find a way to delete it, but, alas... It is here for all to enjoy my moment of unclarity

[Søren Dyhr]

Although "Flagging" by and large isn't fully embraced, haven't Filemaker Inc not found it nessersary to delete it from the commands:

http://www.filemaker.com/help/Script-Steps52.html

--sd

[bcooney]

If this is a multi-user system, the Replace command may fail if any of the records are currently open by another user.

You may wish to consider a looping structure that Opens the record explicitly, and if successful, sets the field. Otherwise, trys again (maybe 500 times) and if it still fails, notifies the user of the failure.

[Agent Ska]

I suppose I should have replied with my solution because I am not sure how flagging came into the discussion... but here's what I used - it was so basic, I had just forgotten it for a moment.

Loop

   Set Field ["orderStatus"; "In Production"]

   Go to Record/Request/Page [Next;Exit after last]

End Loop.

--too easy...

[LaRetta]

--too easy...

And also breakable!

The reason Søren and Barbara were discussing 'flagging' is that your Set Field[] will NOT SET a record if it is multi-user and another User is modifying that record!! Also, Replace Field Contents[] is faster than a loop although it can't be error trapped. You would be wise to listen to them and include error trapping.

Edited December 1, 2008 by Guest
Added sentence

[LaRetta]

BTW, Replace Field Contents[] does throw an error (201 field cannot be modified). But it doesn't say which record failed (like Set Field[] would do). If using, Replace Field Contents[], one could always perform the same action again to find any records which didn't set properly the first time (even within an loop). Much depends upon the needs and the process and we don't have enough information at this point.

[Søren Dyhr]

With CF's and GTRR(FS) possible, do I consider "flagging" as a bygone!

Looping and scripted replaces do not scale particular well, well the same could be said about some recursive custom functions if you turn them tail recursive:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000663

...which solves the problem partially, but if the found set get too huge, must a clipboard saving/restoring algorithm be included with the old Copy All Records via a utility layout.

That being said, should the by fm8 introduced GTRR(FS) be used, since it ignores in which of records the set of primary keys might reside and merges all the sets into a key... could it be done like the template I here include!

Since the record locking issue is an urgent design matter, have I taken near extreme measures to circumvent writing to an occupied record.

--sd

demo.zip

[LaRetta]

I'm sorry ... but we cannot get away from writing to records!! That is what we do with Set Field[] and nothing can eliminate it. If we need to flag records as posted (either by inserting a PostedDate or whatever), we need to set the field. Once the field is set, privileges can restrict access, posted records can be omitted from relationships and so on.

I, too, understand using IDs as you describe but you have yet to convince me that changing data in records is unnecessary. And since it is necessary (and if used in multi-user situations), record locking IS and SHALL REMAIN an issue that we need to address.

UPDATE: In fact, YOU change data in records. And you must trap for record lock as well. What makes you think that, having all the "In Production" record IDs in ONE record does any good whatsoever when there are relationships, searches etc. which will filter/find according to their status? Why not set the fields directly?

What if this one record was deleted? What would then happen to the "In Production" set?

Hey, I'm open and have been. But these discussions come from you periodically and I am boggled by the complexities you will go through NOT to ever change the data. Instead, I suggest solving the issue of record-lock in a thorough and simple manner.

Edited December 1, 2008 by Guest
Added update or two

[LaRetta]

Regarding flagging ...

I disagree with flagging records for a specific USER, ie, they want to work with a found set etc. But I do not disagree with flagging records by setting them as Printed, Shipped, Posted or any other function which affects the data for ALL Users and is part of the necessary data-change within each record. We are talking here about putting records into production. This would be true for any user viewing the record and I would assume the User should then be prohibited from adding lineitems etc.

When data change affects all users, it is not flagging; even though both data-setting and flagging would hit the same record-lock issues.

[Søren Dyhr]

Why not set the fields directly?

Because the info stored is basicly redundant and in worse case scenarios could take quite some time to accomplish. Something you only would expect foreign-key fields exhibiting as virtue.

What if this one record was deleted? What would then happen to the "In Production" set?

That's truly a weak spot, in the present template - the aggregated keys should be stored a relation away in a dedicated table, in a way the related record doesn't disappear if the record where it's created from, gets deleted. I'm not even sure the two tables needs to be related more than once.

But these discussions come from you periodically and I am boggled by the complexities you will go through NOT to ever change the data.

I'm simply trying to make Boyce and Codd's rules, at least have some bearing with filemaker as tool. Mainly because my math skills are inadequate to worm myself thru their mathematical proofs, by instead learning these by heart first and then later see them fit ... if possible.

I think a fair estimation of the roamers here, is a vast majority first and foremost an entry to database approaches via practical solution, where the intellectual sense making arrives a little later - something that have carved filemaker unique niche, where the competition requires skills before even thinking up a solution.

This doesn't mean that recognition of a established skill set neither should nor could be ignored for ever. This includes the daring of questioning established methods as well!

This is quite like the building of a house, where you possibly can get away with it, to a certain extend to build without knowing the trade thoroughly ... but if you some day should be in a situation you would need to sell it again, would your performance be extra scrutinised if you aren't a tradesman. At least would a tradesman better know where to hide flimsy matters if he were to save a penny here and there.

I, too, understand using IDs as you describe but you have yet to convince me that changing data in records is unnecessary.

Here a tiny moment later have I tinkered a little with my approach, to deal with your excellent catch - which I think have been solved ... this time shouldn't record locking be an issue?? ...while the scripting shines by getting even simpler!

--sd

Edited December 1, 2008 by Guest

[LaRetta]

I don't discount anything you suggest and will consider it further. But you currently still fail to convince me in any meaningful way, sorry. [color:green]There is no redundancy at all!!!

Why have a field called PrintDate if we don't plan to insert the print date? Why have ANY field if all we plan to do is take the IDs and store them in arbitrary multilines in another table? So you have a field called CustomerIDs, a field called SupplierIDs, called PrintedIDs, called

You will then need to translate the data back again ... You will need a table occurrence for every field (which holds the IDs of records meeting that table occurrence' criteria). You will need a table occurrence of customers related to that CustomerIDs field; a table occurrence to that SupplierIDs field; to the PrintedIDs field.

Where do you draw the line, Søren?

If you simply set another table with Type = "Customers" IDs, how can you use that information to filter a current portal (based upon a value list of Type) that is a multi-criteria with "Customer" being one part? Relationships will not work well that way if part of a record's meaning is stored in another table. Same is true for 1:1 relationships where sometimes we store multiple fields belonging to a Customer in another table. There is nothing wrong with it but you had better be sure that you don't need any of that child information in a relationship in your main Customer's table!

So the main reason I disagree with your approach ... each time you multiline your IDs elsewhere to indicate a data set, you remove the possibility of including that field (Type = Customer) in a multi-criteria relationship because that DATA (by you) has been moved to a different table than the original record.

Ahem ... no thank you. You play your setting fields with multiline IDs all you wish. I'll set the fields within the records. It is simpler; that is why fields are there; record-lock protection is NOT that difficult and it is why the RAM-Gods invented Set Field[] and Replace Field Contents [] and other such tools designed to change data within a table. Again, we are setting data which will be the same for all Users - it is NOT User-specific.

Theory is fine. Twisting theory to a literal-but-illogical direction isn't.