How to build list of found IDs?

[K1200]

Following a scripted Find, I need to build a variable with a simple "concatenated" list of found IDs. I thought List(Table::RecordID) was the answer, but all I get is the first ID ... not the CR-delimited series of IDs. The Help file hasn't helped me on this one.

Thanks in advance for any assistance.

[LaRetta]

You can write to variable called $IDs as

Set variable [$IDs ; List ( $IDs ; table::ID ) ]

:-)

EDIT ... so you can loop but you can also go to a layout with only the ID and Copy All Records. And you can use custom function. Best approach would depend upon context and purpose.

EDIT2 ... iPad doesn't display your version. If 12, you can also use ExecuteSQL()

[K1200]

You can write to variable called $IDs as

Set variable [$IDs ; List ( $IDs ; table::ID ) ]

Thanks, LaRetta ... but I still end up with only the first ID. In Script Debugger, I watch my GTRR call up a table layout that displays six records. Then it executes the above Set Variable. The result is the first ID only.

I feel like I'm looking right at the problem, but I just can't see it.

(BTW. I'm running 11.0v3)

[LaRetta]

You would need to loop through the records each ID being added.

[bruceR]

What will you be doing with the variable once you get it?

A custom function can also do the job; such as:

http://www.briandunning.com/cf/1093

or

http://www.briandunning.com/cf/1058

[LaRetta]

After found set use this pseudo-script - not easy to type into on iPad without my Apple wireless keyboard. Do you really need me to type the loop script also? Just in case, it is basic as. ...you have your found set and ...

Loop

Set Field ... As above

Go to record/request [ next exit after last ]

End loop

:^)

Hi Bruce,

Yes I asked the purpose as well. I was also thinking of Agnes Riley's calculation.

[bruceR]

You can also perform a replace operation on a global field with a two line script:

Set variable [ $$IDList; ""]

Replace Field Contents [ zYourGlobalField; Let( $$IDList = List( $$IDList; contacts::pkContactID); "") No Dialog

[K1200]

You would need to loop through the records each ID being added.

Well, that makes more sense.

It also works.

Thank you.

Regarding purpose: I'm then finding the position of a particular ID within the resulting variable in order to scroll a portal to display the proper data set. Maybe there's an easier way, but I've confirmed that this does work.

[comment]

I'm then finding the position of a particular ID within the resulting variable

Couldn't you use List ( Child::ChildID ) in the parent record?

[LaRetta]

If you are scrolling the portal then can't you use simple calculation in the script of

List ( portal table::ID )

... and then no need to GTRR. I am probably missing something? Are you sorting the portal or the relationship?

I would be interested in speed tests but I like Bruce' approach also. You just want to know the position of the current portal record? How about a calculation unstored number with Get ( RecordNumber) in the child table? Still not enough information (for me) to make better suggestions. :^)

Oops, typing on iPad keyboard is painfully slow ... I didn't see your response, Michael.

[bruceR]

Regarding purpose: I'm then finding the position of a particular ID within the resulting variable in order to scroll a portal to display the proper data set. Maybe there's an easier way, but I've confirmed that this does work.

Easier way for what?

What has the portal got to do with the found set?

You're providing very incomplete information.

[K1200]

You're providing very incomplete information.

Yes, this happens when I try to extract one aspect of an otherwise complex process for the sake of asking a simpler, more direct question.

I have a filtered and sorted portal. While viewing the portal, the user acquires a particular record ID through an entirely unrelated process (i.e., assume someone walks up and says "can you show me record X0012?"). The user doesn't want to scroll through 200 portal rows to find the entry. So, I've built a script that does the same Find/Sort as the portal and then locates the ID within the found results (= portal's displayed results). And now it works. But a simpler method would be great.

What FileMaker provides: Go To Portal Row

What is also needed: Go To Portal Row where ID=X0012 (or some other direct match)

Does that help?

(BTW, the displayed records are located in the "off limits" side of a separation model database, so adding calculated fields can't be considered at this time.)

[bruceR]

Can the sorting/filtering be set up at the relationship level or does it need to be done only as a feature of the portal? The List function works for related child records but it doesn't match the portal contents if only the portal is filtered/sorted.

[K1200]

Can the sorting/filtering be set up at the relationship level or does it need to be done only as a feature of the portal?

It would require adding a TO and redirecting three portals to it, since the current TO is used by several processes. Given the choice of adding a TO or having a triggered script, I'll stick with the script. But a portal-centric calculation would be best.

[dansmith65]

If the portal is the active object, then GetNthRecord() function should accesses the filtered records, and not all records from the relationship. Try a script like this:

Go To Portal Row [ first ]  // this might not work if you have two portals - in that case use go to object, or go to field

Loop

    Exit Loop If [ Let( $i = $i + 1 ; not isvalid( GetNthRecord( PortalTable::id ; $i ) ]

    If [ GetNthRecord( PortalTable::id ; $i ) = $YourIdToFind ]

	    Go To Portal Row [ By Number ; $i ]

        Exit Loop If [ true ]

    End If

End Loop

Edited August 28, 2012 by dansmith65

[K1200]

Well, it seems I've again left out some details. There are three portals involved ... on separate tabs. What I'm doing right now (after my script finds the row number) is Freeze ... Go To Object / Go To Row ... Go To Object / Go To Row ...Go To Object / Go To Row. The user never notices anything other that the portals magically lock onto the needed entries. It seems more efficient than the scrolling method you've suggested. Are there other considerations?

[dansmith65]

It seems more efficient than the scrolling method you've suggested. Are there other considerations?

The method I mentioned will do the entire operation: find the record with the matching value, then go to that portal row.

[comment]

I've built a script that does the same Find/Sort as the portal and then locates the ID within the found results (= portal's displayed results).

How exactly does it "locate the ID within the found results"? How can you even be sure the ID is among the found results?

While viewing the portal, the user acquires a particular record ID through an entirely unrelated process (i.e., assume someone walks up and says "can you show me record X0012?").

If it's an unrelated process, does it have to be in the same portal? And if yes, wouldn't it be simpler to filter the portal further so that X0012 is the only record shown (again, assuming it's in the filtered set to begin with)?

[K1200]

How can you even be sure the ID is among the found results?

Wouldn't it be simpler to filter the portal further so that X0012 is the only record shown?

comment, those are good questions, but not relevant in my particular application. First, the requested ID is guaranteed to be in this portal. It's just a characteristic of this particular layout. Second, the portal must still be capable of scrolling to any available record. I just needs to assist the user by positioning to the requested one.

dansmith65, my interpretation is that your suggested method would have to be implemented for each of the three portals. Is that incorrect?

Thanks for the responses.

[dansmith65]

dansmith65, my interpretation is that your suggested method would have to be implemented for each of the three portals. Is that incorrect?

yes

[comment]

I am not a big fan of looping in the portal, but since this is merely a user assist, I suppose it's all right. Anyway, if the row # is the same for all three portals, then once you have finished the loop in the first portal, you can grab the row # and use it in the other two.