What does first record number represent? If you include a sort, the first record number will always be the first record.
I would include a sort by Department Number, then Item number, then you could select record number "1".
Steve, has a good question. I would add : Do you want to go to the first record created? When I looked up the Script step Go to Record/Request/Page, the options I have are (First, last, previous, next , by calculation) did you choose another script step. One method I use to get around this sort of issue. Is to sort the records in a manner that the record I want to go to is the first record. I might sort by creation date ascending if I want the oldest record to be first. If I'm looking for the last record created I would reverse the sort.
Tell us more about what constitutes the "First" record for a department.
Thanks for the quick reply. Maybe I didn't explain it right. the data looks like the sample above. It is sorted by department name, then by item number. Let's say I open the layout in browse mode, and I'm sitting on the first record (Department1, Item1). Then I click the popup menu, and choose "Department3" from that menu. I want to jump to the Department3 Item1 record in the current found set. I don't want to change the found set, just jump to that record. Not the ACTUAL first record. Since there are different numbers of items in each department, I don't know how to find the record number of Department3 Item1.
BTW, it may not always be "Item1". That department may start with "Item3". I just want to find the record number of the first record for Department3, given that the records are sorted by Department then by Item.
Thanks, Paul. Your reply came just as I was replying to Steve.
The records, as mentioned before, are sorted by Department+Item. I don't want to change that, nor do I care about actual order created. I just want to jump to the first Department3 (for example) record, given the current sort. Since, in my popup menu, I am choosing only the department name, that is all I have to go on. How do I translate a particular department name to a record number which equates to the first instance of the records with that department name, given the current sorted result set?
I assumed that Go to Record/Request/Page, with the By Calculation option was the proper method. If not, any ideas what to use? If it is the proper method, I would need that record number, right? I don't see how I could sort by that, then take the first record, then resort, because I may have chosen "Department2" instead.
Hope this helps, and thanks again...
1. When you say "...Then I click the popup menu, and choose "Department3". What are you clicking--a field, or somthing in a custom menu bar at the top?
2. When you click "Department3", do you want to see a sorted list of all the records in Department3, starting with the first record? If that's the case, then you could use a script trigger that would take you to the related records and sort. If sorted in ascending order, the lowest number will always be first. So if in Department 3, the first record is is Item 24, item 24 will show up as record 1. You could also do this in a new window so as not to use your 'found set'. But......
I don't understand...."I don't want to change the found set.." When you open the layout in browse mode, how does it get to the state it's in. Is there a layout trigger that sorts the data? If it's an OnRecordLoad script trigger that sorts the records, every time you go to that layout it will put the records in that sorted order.
After a sort, did you try Go to Record/Request/Page and choose "First"
I'm guessing you are suggesting sorting so the department I want is first, then going to the actual first record (with this new sort), then re-sorting back to Department+Item, leaving my active record as the first one from the department I chose. So in the sample found set above, I choose "Department2". After finding the proper record and resorting back to the original order, I would be on record 3, right?
Unless I'm missing something, I can't do that. If the records are sorted as originally stated above, then I choose "Department2" from the popup, how can I sort the found set so Department2 is the first record, given I have Department1 and Department3 records?
Hold on, I just had a flash. Do you mean do a find for the selected value instead of a sort, then goto the first record, then cancel the find (show all records)? That would work. In fact, doing it manually, it does work, sort of.
The only problem is that if department names have similar words in them, the find isn't exact. Consider this example, which is closer to actual data:
Department One Three Five
On my layout, the Department field is also a popup menu. When I click the Find icon, then click into the Department field for my criteria, the popup menu displays a list of ALL departments. So I select "Department Three" from the popup, then click Perform Find. Instead of finding "Department Three" records, I find "Department One Three Five" records AND "Department Three" records, because both satisfy the find criteria.
Is there a way to make the find literal? IOW, find the records which exactly match the find criteria instead of matching the individual words in the criteria? That would solve the problem.
That looks like it would work. Since I'm so new at FMPRO, I may be asking a dumb question, but maybe I don't even need the temporary variable? Since the global field DeptFind was loaded by picking a department name from a popup menu of department names, couldn't I just use DeptName instead of the variable? Come to think of it, maybe don't even need the error code for the same reason, right?
The only downside I see is that if there are many records in the table, and you pick the last department in the list, it could take a long time to find it, right?
If I first do a find based on DeptName I would have a smaller result set, and your technique would also prevent the problem mentioned in my last answer to Steve above.
"maybe I don't even need the temporary variable?"
maybe, but it is there because, if the Department isn't in the found set, I wished to return to where I start.
"maybe don't even need the error code for the same reason, right?"
the answer is the same.
"it could take a long time to find it, right?"
Not so much, but could be shortened.
"If I first do a find based on DeptName I would have a smaller result set"
Yes, but you'll lose the starting found set.
1 of 1 people found this helpful
How big is your found set? There are several possible approaches to this.
It may seem like it's a simple request but - with the current feature set - it isn't simple.
It isn't terribly difficult; just not trivial.
Here's one approach, looking for a value in the zip code field of a found set.
Not suitable for large record counts.
Takes a few seconds if the record position is 1,000.
In my test case, it was basically instant for position 300.
The script could be improved to handle a not-found condition.
Set Variable [ $target; Value:"98052" ]
Set Variable [ $k; Value:0 ]
Set Variable [ $fc; Value:Get( FoundCount ) ]
Exit Loop If [ Let( $k = 1+ $k; GetNthRecord( Zips::Zip; $k ) = $target ) ]
Exit Loop If [ $k > $fc ]
Go to Record/Request/Page [ $k ]
There is a very simple solution to your quest.
You don't need to know the record number of the record you want to select, because the Department Names can be used to select it for you.
I attach a FileMaker database which demonstrates the solution, but for everyone else let me explain how it works.
Firstly, you need to to add a relationship between your Department Selector field (Text Global) and in a new Table occurance (Relationship2) of your data, link to the field 'Department'.
Next you need to construct a one line script, using the script step 'Go to Related Record'.
Set the 'Go to Related Record', so as to use the new Relationship (Relationship2)a nd to show the result on the current layout.
Do not tick any other settings in the GTRR set up box.
Set this script to run using a Script Trigger (On Modify) attached to the Department Selector field.
Now when you select a department, the script runs, which matches the first record with a matching department name to the Department Selector field. Since you haven't checked either of the two lower boxes in the GTRR set up, the found set will not change, but the required record will be selected.
If you have trouble with this, then come back and see if we can explain more about it.
Best wishes - Alan Stirling, London UK.
that is nice.
Only you'll have to sort the relationship to be sure to go ever to the first item of the department.
And, if the first item of the department isn't in the found set, your script will change it ( the found set )
Here is how I solved the issue, before I saw your post, which is a combination of Steve's and Daniele's suggestions:
If [Departments::DeptFind <> ""]
SetVariable [$Find; Value:Items::DeptFind]
Perform Find [Restore] (criteria: Items::Department ==$Find)
Go to Record/Request/Page [First]
Show All Records
Sort Records [Restore; No Dialog] (criteria: Items::Department, Items::Item)
Set Field [Items::DeptFind; ""]
This does not require knowing the record number. If I haven't chosen anything in the DeptFind global field, nothing happens.
I am intrigued by your idea, however. I've been a programmer analyst for many years, but am stumbling around a bit trying to learn FileMaker Pro. It's quite different than what I'm used to.
Based on the tutorials I've been through, yours is an excellent idea. I'm concerned with how much overhead, if any, the new instance brings to the database. In Access, you can define as many queries as needed, and just execute them. In FM, apparently, table instances are the equivalent.
Can you give me any insight on the merits of the above method vs. the new instance method you proposed? I'd really appreciate it.
Thanks again for the idea!