finding position of a record using key in a found set

[jjjjp]

I may be having a hard time doing something that isn't so hard, but I can't come up with an elegant way to write script that finds the position (record number) of a record in a found set sorted alphabetically by one of its fields. The records are of teams in a schedule, each with its unique id, and I sort them using a field called Listing (which gives the team members). I know the id (TeamID) of the record whose position I want to find.

If I search for TeamID after sorting the found set and then perform the Show All Records step, I can get the position within the whole set of records, but I want its position in the found set (records marked as active along with a few other limiting conditions). There seems to be no way of showing all records from the last find without losing the focus on the specific record whose position I am trying to determine. Or is there?

I also tried doing a find that limits Listings to those whose text value is less than or equal to the listing of the record in question. But Filemaker seems to have trouble with < and > in the search if there are internal spaces. Using in front of spaces doesn't seem to help. I take it these operators are really for numbers. Is there some other way to do comparative alphabetic search on strings?

I also tried marking a specially defined field of the record whose position I want. Then I could sort descending using that field, go to the first record, sort again in the sort order I ultimately want, and do a Get (Record Number). But if other users set that field as well, I am in trouble, and I can't reset all values to "" because other records may be locked.

I guess I could loop through each row of the found set until I locate the record with the right id, but Filemaker always seems to provide a more elegant and efficient way of doing things, so I figure I must be missing something.

The whole point of this is to go to the correct row in a portal after I am done performing operations in the equivalent table. (My search criteria in the table layout mirror the search criteria in the portal layout.) So if there is some portal-based way to locate the row number using an id without searching row by row, that would be fine, too. Surely this is a routine operation with some simple solution. The fact that I can't seem to find the problem on the Forum suggests I am missing something obvious.

[bcooney]

I'm not following the work flow. You're "performing operations in the equivalent table," and then going to another layout (different TO) where there is a portal, and you wish to be on the portal row for a record that you just edited?

[comment]

This part is not clear:

The whole point of this is to go to the correct row in a portal after I am done performing operations in the equivalent table. (My search criteria in the table layout mirror the search criteria in the portal layout.)

A portal has nothing to do with the found set in either table. It always shows ALL related records, based ONLY on the predicates defined for the relationship.

[jjjjp]

I think I can answer both questions at once. The portal, in let's say Layout 1, is based on a cartesian join to itself, and it displays team records alphabetically according to the listing field with only records displayed that meet the conditions laid out in the portal filter. A user can ask to edit the team by clicking a button on a portal row. The script then moves over to Layout 2, which is based on the teams table (same TO as the related records in the portal), with a portal displaying the team members for the team. The user performs operations on team members (add members, deletes them) and clicks a button to go back to the original portal displaying teams.

At that point the focus is lost in the portal displaying teams. I'd like to go to the portal row the user started out in, which may have shifted as a result of any changes. My plan was to compute the portal row number in Layout 2, where I can easily do sorts and finds on the team records displayed in Layout 1, while reproducing the exact conditions contained in the portal filter. (Is this a wrong-headed way of going about things?)

I hope this makes things clearer, not muddier!

[comment]

If the portal is being filtered, then finding the portal row by TeamID is not quite straightforward. You could store the originating row number in a variable at the beginning of the script. Or open a new window for the editing, then close it to come back.

Note also that immediately after GTRR [show related only] called from a portal row, the records are sorted by the same order as the portal - so the current record's number is equal to the originating portal row number.

[jjjjp]

The problem here is that the original position in the portal doesn't generally equal the final one, because the records shift positions as the user modifies the team members.

[comment]

I am not sure why TEAM records would change position as a result of modifying the team MEMBERS.

Anyway, which portal row do you want to come back to? The one that the user clicked, or the one with the current record in the edit layout?

[jjjjp]

The team members are ordered alphabetically by a string containing the last names and the units (colleges) of each of the team members. When the team members change, their relative position in the portal changes too.

I want to come back to the team the user clicked on in Layout 1 (teams in a portal) before manipulating team members in Layout 2's portal. Not exactly the same row, but the same related record in the team portal. Because I apparently have to commit records before moving to Layout 2, even though I am moving to a new window, I lose the original focus; i.e., the original portal record is no longer selected when the user goes back to Layout 1.

Thanks for your help so far.

[comment]

The team members are ordered alphabetically

But what has this to do with the order of the TEAMS in the portal?

[jjjjp]

The team members are ordered alphabetically within the string by last name, then first name, and the teams are ordered alphabetically by the resulting string.

e.g.

Allan Baxter (University) / Jim Baxter (New)

Barbara Adams (Trinity) / Jim Chandler (University)

The order doesn't have much special significance, but it's what I have to work with if I am to be able to select the portal row after a team has been edited. Even if I left the portal unsorted, the problem wouldn't, I think, be simplified any.

[comment]

Even if I left the portal unsorted, the problem wouldn't, I think, be simplified any.

I am not entirely convinced about that. The choice of the sort order does seem a little strange, in any case.

I am still missing most of the picture here (perhaps you should post a simplified file). If during editing the user stays on a single record and does not modify the found set, and the found set is sorted in the same order as the portal, then the record number is equal to the portal row number.

But there are many assumptions here that may not be true. We don't even know how the portal is filtered. In theory, at least, once the user has edited the record, it may no longer even be in the filtered portal.

[jjjjp]

You're right that leaving the portal and table unsorted would mean that the records won't shift after they are edited, but there is another problem at play: if another user were to add or delete a record, then remembering the portal row will not produce an accurate result 100% of the time. I may be too much of a purist, but I would prefer not selecting the portal row at all to selecting it with a tiny chance that it may not work right.

If the table is sorted, then the row will definitely shift after it is modified.

You are correct as well that the row may not necessarily appear in the filtered list. But that can be tested. If the record doesn't appear, then the script doesn't select a row.

Finally, you are right that my method of sorting teams is a little strange, but there is no way to sort based on the team members records themselves since all that I have access to in the Teams table is their team member ids. So I used the text representation of a team that I maintain as a field in Teams as (what seemed to me) the best alternative.

As requested, I'm attaching a simplified file. It selects the new row number in the portal, but it will produce an error if another user concurrently has any record in the teams table open. So this solution will need to be replaced, but at least it shows what I'm after.

I can also upload my database to the Dropbox website if you think that will help.

Note this problem isn't make or break: not showing the user the row s/he just edited is not the end of the world. But there are other situations where I would like to do the same, and I am feeling challenged by my inability to come up with a neat solution to this problem.

It might be useful to think about this in the abstract: again, how in general can one find the record number of a field with a known id in a found set ordered alphabetically by some other field.

Is there some way to do a find of a text field that yields all records alphabetically less than a given text string? If there were, then I would have a solution.

demo.fp7.zip

Edited September 2, 2010 by Guest

[comment]

Would something like this work for you?

I suggest you also look at these:

http://fmforums.com/forum/showtopic.php?tid/214676/

http://fmforums.com/forum/showpost.php?post/364244/

my method of sorting teams is a little strange, but there is no way to sort based on the team members records themselves

Actually, there is - but what I found strange is the requirement, not the method. In my mind, a team is an entity of its own and I would expect it to have some permanent attributes - like a name that doesn't change with the team members.

demoAlt.zip

Edited September 2, 2010 by Guest

[jjjjp]

I think that might work. I'll have to study it more and see if it works in my original unsimplified example. I'm suddenly facing a deadline but my initial testing looks good. I'll re-open the thread if I run into trouble when I get back to this.

I used to move around all the time from portals to tables using GTRR rather than by switching layouts and performing a find. Then I started writing some more general scripts that relied on setting fields by name, etc., and GTRR didn't work in these situations. I got into the habit of relying on finds, even though the coding isn't as concise. It appears that GTRR doesn't result in a loss of the focus in the portal whereas using a find does, even if when I use your neat trick of going to the portal row before changing layouts. (I'll have to test this some more.) So there are, after all, some distinct advantages to GTRR?

Many thanks for your diligence and your insight.

[comment]

It appears that GTRR doesn't result in a loss of the focus in the portal whereas using a find does

I don't think so:

demoAlt2.zip

[jjjjp]

Yes, you're right. I must have been sloppy in the test I performed. Well, that's a relief. So the key is simply to go to the portal row before changing layouts. How nice and simple.

I take it that if I have to commit records in the portal to avoid a conflict with my later work in the table, I would still do that before going to the new layout. Or does going to the row somehow commit that record?

[comment]

Actually, the key is to do nothing in the current window and come back to it exactly as you left it. I added the Go to Portal Row step only for testing, because clicking the button alone doesn't select the row.

If you commit the record, you will also lose the current portal row - so if you want to keep it selected, you must reselect it before continuing with the rest of the script. This could be a problem, because committing will also resort the portal - so we are back where we started.

[jjjjp]

I see, but I don't quite get how we were able to edit the very same record in the table without first committing the record in the portal. I'll have to test this solution out on my database as soon as I get the chance to see if my scripts ever get blocked for trying to open an active record.

[comment]

I wasn't suggesting you shouldn't commit the record. I believe you should either re-think your overall approach or settle for looping among the portal rows until you find the TeamID.

One possible change could be disallowing record modification in the portal and restricting it to the edit layout only. I have also pointed you to two demo files that IMHO present a better solution to the problem.

[jjjjp]

Yes, I'll think about looping but will also spend some time with the two demo files you sent. Thanks again for all your help.

[jjjjp]

I finally had a chance to get back to this issue and to read the links to the posts you provided a few messages back in this thread, and I think you were right to suggest that they would offer a solution. I didn't need anything quite as sophisticated as a repeating global variable; it seemed to be the application of conditional formats that made things work. I am attaching the modified example should you want to have a look, or should anyone else have been so hardy as to follow the thread this far. Note that I set up script triggers to clear the global variable storing the team id before and after entering the layout; that way I can re-use it for other similar situations.

Once I understood what to do, it was a bit tricky to get the portal row to look as though it was Filemaker that selected it, but again your example gave me enough of a start. (Actually, I couldn't get it exactly as I wanted, because I usually have a light grey box around my data, and Filemaker frustratingly seems to want to turn the lines to white when they are juxtaposed over another colour.)

Thanks again for leading me to what appears to be a satisfying solution.

demoAlt3.fp7.zip

Edited September 8, 2010 by Guest