Replacing an entire field for all records

[stubanz]

In my database, I use a script to weed out duplicate entries. It worked well until I started using a "flag" field that I created for the script with other scripts. In the script, I use Set Field ["Flag",""""]. What I wanted this to accomplish (-and thought that it did, until recently), was to clear the flag fields for all records, so that it would flag the results I wanted it to, and keep all of the records that were unflagged after the script. Unfortunately, the Set Field ["Flag",""""] command only clears the first one. Thus, if all fields are flagged before the script, the first record (which was cleared) is the only one that shows up unflagged.

To remedy this problem currently, I just change the Flag field to global, then back, which has the same effect as clearing all the fields in every record. The problem with this method is that I want other users to use the database, and I feel that this is a pretty unprofessional way of doing things. Any help is greatly appreciated. Thank you!

[SlimJim]

You need to use the script steps

Show All Records

Replace Field Contents["Flag";""]

[Vaughan]

SlimJim is correct about the Replace command.

However any process that relies on setting a "flag" field will break in multi-user mode, because the field will keep getting set and re-set by each of the users... possibly simultaneously.

[stubanz]

In that case then, how would you make a script to remove duplicate records? Not remove, exactly, but make it so that they are omitted when looking at the data? I was looking through at a thread that had some good ideas, but they all involved the flag column, to my knowledge.

[BobWeaver]

You can still use a flag field in multi-user mode, but you need to use it differently. Create a unique session key for each user when they begin. I generally concatenate date, time and a random number adn store it in a global field. Then when you want to mark a record, append the session key to the current contents rather than overwriting the contents, like so:

Replace Field Contents [Flag, Flag & "

[-Queue-]

Hmm. It seems like a Set Field loop would be more reliable here since you can check during each iteration whether a record is locked. But, why not use a global containing the desired ids instead? Then there should be no multi-user gotchas.

[BobWeaver]

Yeah the global would probably work better here.

I've used the flag field technique in the past because of the 64K text limit in older FM versions, and also so that the user could save multiple found sets using different key values.

Old habits die hard.

[stubanz]

You mentioned earlier something about records being locked and a replace function not affecting it. I think this is happening to me. I fixed the flag script so that when FMP starts, it works, replaces everything and flags and omits, like I want it to, but subsequent flags can't be undone by the replace command. It feels like I'm locked here, and I want to be unlocked. Any suggestions?

Right now I want to fix the flag script, and will hopefully try the other ideas soon. (Basically a thanks, but I don't quite think I'm at that level yet - only been using this think for about two weeks Again, thank you guys for all of your help, this has been an invaluable reference.

[stubanz]

I was reading back through the forum, and I became a little confused...doesn't a global field have only one value? How would it be able to check whether the field is locked, and contain the IDs?

[Vaughan]

Rather than marking each record you want to remember, you make a list (in the global field) of the record ids that you are want to remember. Since the records themselves are not being edited in this process there is no record-locking problem.

However this technique does have a different limitation: in FMP 6 and earlier the limit to the amount of data stored in a text field is 64 kB, so there is a limit to the length of the list that can be made. If the record ID string is 7 bytes this usually equates to about 8,000 records that can be flagged this way.

This text field limit was increased to 2 GB in FMP 7, which is about 250,000 records.

[stubanz]

Okay, now I see. How exactly would one do this? I see that the records are not being modified, and I see that they I want to remember them in a global field. My guess is that I would start with this: (Go to First, Loop, Next [exit after last], end loop), putting the if statement inside the loop, and the command that makes the ID remembered inside the if statement. What exactly do I do after I say, for example:

Loop

If [custID = "gSavedCustID (-a global field to make sure it's not a duplicate)" and endDate = "gSavedEndDate (dupFinder again)"]

I assume it would go here, but I'm still confused about the global thing and need to know what I should put

Else

Set Field [no dialog, gSavedID, CustID]

Set Field [no dialog, gSavedEndDate, EndDate]

End If

Go to next

End loop

Thanks again for your help!

[-Queue-]

Set Field [gSavedID, ""]

Set Field [gSavedEndDate, TextToDate("")]

Go to Record/Request/Page [First]

Loop

Set Field [gSavedID, gSavedID & CustID & "