Go to record with a field value equal to a variable based on Get (recordID) ; retaining current found set

[Bobfmf]

SHORT VERSION: I'm looking for a good way to go to a record based on a variable $flaggedRecordID initially set from the value of an active field (it's calc is: "Get (RecordID)", but where that field is no longer active (i.e., we're parked on a different record).

DESIRED OUTCOME OF THE *ENTIRE* SCRIPT: Create a set of records (4, in this case) duplicated from template records, where the duplicates are modified ("DATE" replaced with today's date; this part is working). Then the user does some work with the records.

DETAILS:

I derive a found set (based on some text criteria in a field, and a field 'template' that is checked).

I run a loop (see * at the end for how I run the loop, but the method used *does* work):

(Go to the first record.)

Set Variable [ $flaggedRecordID ; Value: Get(RecordID) ]

(Duplicate the record and untick it's "template" field box.)

(Text in a field is modified.)

(Omit the modified record I'm parked on.)

(Next, I want to go to the (template) record from which the duplicate was derived, omit it, then repeat the loop until all the template records have been duplicated and work is done on the duplicate. Note: I have subsequent (working) scripting that creates a found set of the duplicate records, which are then modified by the the user.)

(I want to go to the record with a field: "recordID" that has the value of $flaggedRecordID. I'm looking for a solution for that. If I simply 'go to record … [first]', then Omit record (the template record from which is the one used as the source for the duplicate, that works. Maybe that's all I need, however it doesn't seem robust, but in any case, not generalizable.

Update: at least for this scenario, instead of even bothering to try to go to a record based on a field with the it's recordID stored in it, by setting and using a variable, I can Go to record… $foundCounter (see code credit example following), which works fine.

--

However besides the use in this particular situation, I'd like to learn how to do this (go to a record based on a field whose value is set to "Get (RecordID) ) in general if that's possible. Perhaps I need to bring in RecordNumber, I'm not sure.

thanks

-- code credit --

* Part of loop code is credited to Lauren Kuhlman on YouTube 2009, something to do with live sorting, IIRC:

Set Variable [ $foundCount ; Value: Get ( FoundCount ) ]

Set Variable [ $foundCounter ; Value : 1 ]'

Loop

    Exit Loop If [ $FoundCounter = $foundCount + 1 ]

    # The work done.

    Set Variable [ $foundCounter ; Value: $foundCounter + 1 ]

End Loop

Edited April 30, 2021 by Bobfmf

[comment]

If all you have is the RecordID, then you must loop over the entire found set until:

$flaggedRecordID = Get(RecordID)

 

51 minutes ago, Bobfmf said:

Perhaps I need to bring in RecordNumber,

That might be simpler, since once you have duplicated a record and omitted the duplicate, the original record retains its position in the found set (provided you're not doing anything else).

However, this entire method seems rather over-complicated. You didn't tell us what's the ultimate purpose of this exercise. It seems you have a bank of "template" records that you use to create "real" records. If so, I would think the "template" records should be in a table of their own - and once you have found those you want to use as the basis for the "real" ones, you can simply import them into the "real" table.

 

[Bobfmf]

21 minutes ago, comment said:

If all you have is the RecordID, then you must loop over the entire found set until:

$flaggedRecordID = Get(RecordID)

That might be simpler, since once you have duplicated a record and omitted the duplicate, the original record retains its position in the found set (provided you're not doing anything else).

Thanks for that!

Quote

However, this entire method seems rather over-complicated. You didn't tell us what's the ultimate purpose of this exercise. It seems you have a bank of "template" records that you use to create "real" records. If so, I would think the "template" records should be in a table of their own - and once you have found those you want to use as the basis for the "real" ones, you can simply import them into the "real" table.

I explained that the script finds select template records, duplicates them (and makes some mods on them one by one in a loop), then the user does some work with the records (after the script completes). The specific work done on the records by the user after the script is run isn't relevant to the script.

The file is currently flat; a collection of 'notes', if you will, and the script is just a 'utility' script. It isn't all that core to the function of the database; it's just one bit of functionality which is used at times. The work the user does is free-form editing of text in a field; they are a form of exercises / routines that can be performed daily.

thanks

Edited April 30, 2021 by Bobfmf

[comment]

[shrug]

Even with a single table, exporting the found set to the temp folder and importing it back would create a found set of duplicates ready for modification, without all that jazz.

One more warning: you say you have a calculation field using Get (RecordID). I am not sure what you use it for, but you should be aware that if you ever move your data to a new file, those values can change.

 

[Bobfmf]

On 4/30/2021 at 12:46 PM, comment said:

[shrug]

Even with a single table, exporting the found set to the temp folder and importing it back would create a found set of duplicates ready for modification, without all that jazz.

 

One more warning: you say you have a calculation field using Get (RecordID). I am not sure what you use it for, but you should be aware that if you ever move your data to a new file, those values can change.

 

Great idea: export / import.

To answer your question, I have a field (recordID) which is a calc using Get (RecordID). Then I used a variable to grab the value of that field when I was running the loop. Then intention was to duplicate a template record, modify the duplicate, the 'go to' the template record based on the recordID field value (which I set into a variable when parked on that record), omit that record, then run the next iteration of the loop until all (4) templates were duplicated, the the dups modified. But, and this was the main question I asked, is if there is a way to go to a record in a found set based on it's recordID. It doesn't appear there is a simple and direct way to do so. And if it were possible, most likely it could be done without enumerating recordID in a field.

Also (and I'm not saying using a field value to go to a record is the best way to do what I'm after), instead of the recordID field, probably creating a primary key field, (even though the database is currently flat) would perhaps make sense, and making the database extensible to being relational should that ever become desired. 

Thank you.

[comment]

57 minutes ago, Bobfmf said:

if there is a way to go to a record in a found set based on it's recordID. It doesn't appear there is a simple and direct way to do so.

That is correct. There is a way to find the record, using either a find or a relationship. But then it would no longer be within your current found set. Or perhaps it would, if you used GTRR without 'Show only related records'? I am not sure I would trust that.

I should add that with a found set of only 4 (or even many more) records, a loop would accomplish this task instantaneously. And, as you correctly surmised, a field is not required for this. But, as I said, I think you have better options.

 

Edited May 2, 2021 by comment