This has to simple but I can't get it!

[NickS]

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

[CobaltSky]

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)

)

[NickS]

Thanks, it worked great.

[NickS]

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?

[CobaltSky]

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.

[NickS]

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

[CobaltSky]

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.

[NickS]

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

[CobaltSky]

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

[NickS]

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

[CobaltSky]

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.

[NickS]

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

[CobaltSky]

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

[NickS]

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.

[CobaltSky]

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!

[Vaughan]

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.