Find Record

[milky]

Hi All,

I have a database with around 1000 players, with a report in List view.

How do I GO TO a particular persons name in the list without doing a Find which only shows that record?

TIA

Milton.

[comment]

I think it depends on how you intend to select that particular person's name. Selecting a value out of ~1000 possible alternatives is not an easy task for the user.

Once you have the selected name, you could calculate its position in a list of names provided by a summary field, defined as List of [ PlayerName ], and use this to go to the corresponding record. The calculation would be something like:

ValueCount ( Left ( sListOfNames ; Position ( ¶ & sListOfNames & ¶ ; ¶ & selectedName & ¶ ; 1 ; 1 ) ) )

Alternatively, you could just loop over the found set until you hit the wanted record. With only  ~1000 records, this would be instant.

 

Edited March 22, 2021 by comment

[milky]

Thankyou Comment,

Maybe if I include some screenshots, my task may be a bit clearer.

I have a script as follows:

Everything works down to the second last command - GTRR, obviously this is where I am stuck with the incorrect commands.

I don't want the records sorted or found, I just want the position of the list to move to where that players name is in the list so I can see what the ranking is

and then copy that record number into a Variable so that I can use it on a report.

 

 

[comment]

I am afraid I have no clue what this is about.

If your purpose is to "see what the ranking is and then copy that record number into a Variable so that I can use it on a report" then there should be no need to go that record. As I already suggested, you can calculate the record's position in the found set with the help of a summary field. But - as I also stated - if you don't want to add fields to your solution, just loop until you hit it.

--
P.S. Show All Records followed by Perform Find does not accomplish anything. 

 

[rwoods]

Hello OP

I think your issue here is you are thinking 'What would a human do if they had a list and needed to find some data from one of the rows'. The human would run their finger down the list, stop when they see the matching name, and then read across to find the ranking.

In a database we wouldn't do that as it is slow an inefficient. If you really did want to do that you could use a loop like this

Set Variable [ $$PlayerToFind ; "Steve Jobs" ]

Go To Record/Request/Page [First]

Loop
	
    #Check if we found the correct record and leave this loop if we have
    
	Exit Loop If [ table::playerName = $$PlayerToFind ]

	#Else carry on looking at the next record

	Go to Record/Request/Page [Exit after Last:On]

End Loop

#We must be on the correct record now, that matches "Steve Jobs"

Set Variable [ $$Rank ; table::Rank ]

#You'd need to handle the situation where we exit the loop without finding the right name

However, much better would be just to ask the database for the value, something like this using ExecuteSQL

Set Variable [ $$PlayerToFind ; "Steve Jobs" ]

Set Variable [ $$Rank ;

ExecuteSQL [ "

SELECT rank

FROM tableOccurence

WHERE name = ? ; "" ; "" ; $$PlayerToFind ]

]

This doesn't involve the long, slow looping through record, and also doesn't change the currently selected record which can be helpful.

Edited March 22, 2021 by rwoods

[comment]

16 minutes ago, rwoods said:

ask the database for the value, something like this using ExecuteSQL

There is a big difference between the two methods: the loop goes over the found set, ExecuteSQL() operates on the entire table. I am under the impression that OP wants to find the position of a specific record in the current found set, under the current sort order.

However, I agree that there should be a better method than looping (although with only ~1k records, that's definitely an option). But for this we would need to know what is the actual purpose of this exercise. If - as I vaguely suspect - this is intended to determine the ranking of a player based on their achievements compared to other players, then it should not be scripted at all.

 

[rwoods]

@comment Yes, you may be correct, the ExecuteSQL on the whole table may be inappropriate, but could be modified to restrict the matching records. It would be good to check it only matched a single record too!

[comment]

Just now, rwoods said:

ExecuteSQL on the whole table may be inappropriate, but could be modified to restrict the matching records.

True, but replicating the found set and sort order in SQL would be inefficient. 

 

[milky]

Thanks rwoods,

You grasped exactly what I was trying to do manually and created a solution to my problem.

I altered your script as highlighted in yellow to get the result I desired.

I even named the script after you!

This allowed me to expand the results, by adding to the script and get additional rankings by sorting by different criteria.

[rwoods]

No problem, I'm glad my name will live on somewhere in the world forever (or as long as your file survives!)

[comment]

But there is a problem. Besides all the other flaws of this method that have been pointed out above (and which the OP apparently chose to ignore), there is the problem of getting incorrect results in case of a tie.