Calculating a sort order

[leahbrooks]

I hope someone can help me with this!

I need a numerical sort order field calculation, based on the sort order of three other fields. I am getting into trouble when I concatenate the three fields, due to Filemaker's nasty habit of dropping preceeding zeros (that I am using as place holders).

Here are the individual fields and some sample data

Reel = 0001 (can be up to 4 digits long)

Scene = 0001 (can be up to 4 digits long)

Shot = 0010 (can be up to 4 digits long)

I have created a text calc field that creates a result:

0001 - 0001 - 0010

This one sorts correctly, but there is a looping script that looks at this field and tries to add +1 to it, in order to move to the next record.

If I make a number calc field, I get the result:

100010010

Which will work for a little while, until (I think) the reel number changes to a two-digit number:

10-0001-0010 would be

1000010010

HELP! please.

Leah Brooks

[Vaughan]

It sounds like you need two fields: a text field with the string used to sort, and a number field used to manage the loop counter.

On the other hand, why use a concatenated text field? Can;t you just sort on the three number fields in order? That is, sort by reel then scene then shot in a single sort order. If they are all number fields it'll sort perfectly.

[leahbrooks]

That's exactly my problem - creating that number field that I can use for the loop counter. I need the number to be able to increment BASED ON those other three fields. I made a text field and then a number field based on the text field, but I'm afraid it won't number things in the correct sort order.

I have an interface file where I have to create a KEY field that will increment and then bring in the correct next record from an external file. That's why I can't rely on sorting by the three fields.

Can you suggest a number calculation that will solve my problem?

Leah

[Vaughan]

I did something like this recently.

I used a global number field as the local key for a relationship and brought the data in that way.

The other method is to keep doing what you are doing, but do it properly: you cannot "+1" to a text field since that's math, but you really don't want to do that anyway. What you want to do is to manipulate the text string to increment part of it.

If your string is *always* in the format "0001 - 0001 - 0010" and you want to increment the last part then you need to rebuild the string again with a process like this: isolate the part of the string you want to change; convert it to a number; add 1 to the number; convert it back to text in the correct format; rebuild the whole string with the new part.

It's early morning here in Sydney Australia so I'll leave it to others to come up with the actual function that does this! It should be possible to do it with a single Set Field [].

[Lee Smith]

Hi Leah,

Did you try the suggestion that Ross Dickson gave you on Blueworld?

Lee

[EvaR]

Show All Records

Go to Record/Request/Page [First]

Loop

Set Field ["TextSort", LeftWords(TextSort, 1) & " - " & MiddleWords(TextSort, 1, 2) & " - " & Right("0000" & (TextToNum(RightWords(TextSort, 1)) + 1), 4)"]

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

End Loop

No number field required. This will add directly to your text field, but will keep the format.

Works for me.