intermediate values calculation

[Capt JB]

Hi,

New on the forum, I am a friendly user of FileMakerPro 7, using it for historical purposes. To make it short, this is my problem and my question

I have serial numbers series composed by numbers like 42-107237 to 42-107248.

I have made a calculated field giving automatically the number without prefix - for example 107237

I am looking to make a calculated field to automatically give all intermediary serials - 107238, 107239, 107240, 107241, 107242, up to 107247.

the field will be used to research any serial and find the corresponding series - find 107240 will give the series 42-107237 to 42-107248 as result

Any help in this matter will be very very much appreciated

[comment]

This part is not quite clear:

I have serial numbers series composed by numbers like 42-107237 to 42-107248

Is there a record for each series, with two fields (series start and series end)?

[TheTominator]

I have serial numbers series composed by numbers like 42-107237 to 42-107248.

What is the largest series that you need? In other words, what is the maximum number of serials that you want to the calculation to return?

If the sequence is relatively short, you could do a "brute force" method. For example for two numerical values SerialBegin and SerialEnd the following calculation will generate up to thirty serial numbers. Add more numbers simply by adding more lines.

Let(

[

numValues = SerialEnd - SerialBegin + 1;

valueSequence =

SerialBegin & "¶" &

SerialIncrement(SerialBegin; 1) & "¶" &

SerialIncrement(SerialBegin; 2) & "¶" &

SerialIncrement(SerialBegin; 3) & "¶" &

SerialIncrement(SerialBegin; 4) & "¶" &

SerialIncrement(SerialBegin; 5) & "¶" &

SerialIncrement(SerialBegin; 6) & "¶" &

SerialIncrement(SerialBegin; 7) & "¶" &

SerialIncrement(SerialBegin; 8) & "¶" &

SerialIncrement(SerialBegin; 9) & "¶" &

SerialIncrement(SerialBegin; 10) & "¶" &

SerialIncrement(SerialBegin; 11) & "¶" &

SerialIncrement(SerialBegin; 12) & "¶" &

SerialIncrement(SerialBegin; 13) & "¶" &

SerialIncrement(SerialBegin; 14) & "¶" &

SerialIncrement(SerialBegin; 15) & "¶" &

SerialIncrement(SerialBegin; 16) & "¶" &

SerialIncrement(SerialBegin; 17) & "¶" &

SerialIncrement(SerialBegin; 18) & "¶" &

SerialIncrement(SerialBegin; 19) & "¶" &

SerialIncrement(SerialBegin; 20) & "¶" &

SerialIncrement(SerialBegin; 21) & "¶" &

SerialIncrement(SerialBegin; 22) & "¶" &

SerialIncrement(SerialBegin; 23) & "¶" &

SerialIncrement(SerialBegin; 24) & "¶" &

SerialIncrement(SerialBegin; 25) & "¶" &

SerialIncrement(SerialBegin; 26) & "¶" &

SerialIncrement(SerialBegin; 27) & "¶" &

SerialIncrement(SerialBegin; 28) & "¶" &

SerialIncrement(SerialBegin; 29)

];

LeftValues(valueSequence; numValues)

)

Edited October 6, 2010 by Guest
Removed List() to make compatible with FMP7

[Capt JB]

Yes, I have two field one for first and one for the last of the series.

[Capt JB]

Thanks a lot, for this.

I tried and it works, but what is the non brute force version of this ??

Regards

[comment]

what is the non brute force version of this ?

This is a recursive calculation, so it can be done either by a custom function (requires the Developer/Advanced version), or by a looping script, or by a repeating calculation field (see attached example).

---

Are you really using version 7? If not, please update your profile.

EnumerateRangeR.zip

[bruceR]

Thanks a lot, for this.

I tried and it works, but what is the non brute force version of this ??

Regards

Consider adding an all_serials table; or making a new file. Create a record set that contains the complete ID list, and set up a relation to the actual data table. Then you can use conditional formatting or find operations to find the missing records.

missing.fp7.zip

Edited October 18, 2010 by Guest

[comment]

Re-reading your original post:

the field will be used to research any serial and find the corresponding series

You do not need to enumerate the series for this. You can simply perform a range find, i.e. find records whose StartValue ≤ searchValue AND searchValue ≤ EndValue. You can also construct a relationship using the same predicates and show the matching series in a portal.

[Capt JB]

Thanks a lot, informative and usefull