I want to come up with points based on Get (RecordNumber). I am sorting by least time elapsed for a race.

1st = 10 points

2nd = 9 points

3rd = 8 points

No points beyond 10 entries

Hello NickS,

I suggest that you set up an unstored calculation with the formula:

Let(Rn = Get(RecordNumber);

Case(Rn < 11 and Get(SortState) = 1; 11 - Rn)

)

Thanks, it worked great.

I ahve a related DB that requires a look up of that field. It is not working now. How can I get the look up to work since calc field is not stored?

Hi,

Rather than using a lookup, I suggest that you set up a script that sorts the original table and then writes the score values (from the first ten records) into the relevant field in your related table.

I already have the sort script. Would I use the Get field contenta nd loop 10 times?

Since you'll be in the table where the values are calculated when the script is running, I'd suggest looping through the first ten records and using a Set Field[ ] command (via a relationship) to transfer the values to your other table.

This is what I have so far but not working. Script created in related table.

Go to Record /Request/Page (First)

Set Field (RTSeriesPoints(8); Race Time::Points Total

Loop

Exit Loop if (RTSeriesPoints::Race Ppoints = 10)

End Loop

Hi,

That's a start, but you need the set field to occur within the loop as well as a mechanism to pass the loop to the next record. Along the lines of:

Sort Records [Restore; No Dialog]

Go to Record/Request/Page [First]

Loop

Set Field [YourRelatedTable::YourRelatedField; CurrentTable::UnstoredPointsCalc]

Go to Record/Request/Page [Next; Exit after last]

Exit Loop If [Get(RecordNumber) 11]

End Loop

I am getting an error message "operator required here". after 11] in second to last line of script.

How can I fix this?

Thanks Nick

Hi,

At a guess, you have copied and pasted the code from the forum, right? If so, the greater-than-or-equal symbol will not have copied (the forum represents it as a graphic object) so you will need to insert it from the list of operators in FileMaker's calc dialog.

No, I did not copy and paste and it still saying an operator is expected here. I tried ) and ] and got the same error.

Hi NickS,

The brackets are inserted by ScriptMaker after you exit the calculation dialog - you should not enter them into the calc. Only the text which appears *between* the square brackets should be entered into the calc window.

So that you can see how it should look, I have created a sample file (see attached) which has the script in it. If you have a look at that, you'll be able to see how the code I provided above should look.

Sample.zip

Thank you for your help. I know you are a professional and usually get paid for this sort of thing. I really appreciate it.

On another note you seem to be around my age, do you remember the band Paul Kelly and the Messengers or the Hoo Doo Gu Rus? A couple of cool bands from back in the day.

Hey yeah. I remember both bands. Still see Paul Kelly's name in the gig guides from time to time, but haven't heard much about the Hoodoos for an age!

Interesting story I have regarding Paul Kelly... in 1991 I went for a holiday to Tasmania, driving from Sydney down to Melbourne, then the ferry over to Devonport. The day before I Sydney left I was shopping in Oxford Street Sydney and passed Paul Kelly as he walked out of a shop.

Then I jumps in my car and drive 2 days to Melbourne, overnight to Tasmania on the ferry, spend a day and night around Launceston then drive to Hobart. Next day I'm walking around the old part near the water (Salamanca Markets?) when Paul Kelly walks past me. Weird.