I believe I'd write this as:
PowersOfTwo ( number ) =
Case ( number > 0 ; Let ( [ pwr = 2^Floor ( Lg ( number ) ) ; rem = Mod ( number ; pwr ) ] ; pwr & Case ( rem ; ¶ & PowersOfTwo ( rem ) ) ) )
Floor ( number ) > 1; 2 ^ Floor ( Log ( number ) / Log ( 2 ) ) & ¶ & PowersOfTwo ( number - 2 ^ Floor ( Log ( number ) / Log ( 2 ) ) )
Isn't better a result that is on one line and can be evaluated ?
Something like ( for 16391 ):
2^14 + 2^2 + 2^1 + 2^0
Ah, missed "Lg"! Thanks comment, much cleaner code. As for the reason I'm 'return-delimiting' each result, is to use them in a relationship to a field in another table. Should be much cleaner than the convert to binary method I'm currently using.
As for the reason I'm 'return-delimiting' each result, is to use them in a relationship to a field in another table.
What's your ultimate purpose in this? I believe it would be simpler to use a repeating calculation field as the matchfield, but I am curious where this is going eventually.
Let's say I have a table with a static set of records, called 'Descriptions', that have a field for the 'power-of-two' and descriptive text. Then, from an XML import, I have a field in another table, called Items, that has the bit-wise OR'd value, and I want to see the related descriptions. So, I create a calculated field, based on this 'power-of-two' custom function, to return a list of separate values. I relate the calculated field in Items to the field in Descriptions, and through a portal I can see the related records.
I've found that a field with return-delimited values work much better than a repetition field. It's based on this excerpt in the FM help file:
You can increase the number of possible matching values by entering multiple values in the match field, separated by carriage returns. You can access related data by matching any single line of your match field, according to your relationship criteria. This is sometimes called a multi-key field or complex key field.
For example, you have a simple relationship joining records in TableA to TableB based on the contents of a single field in each table, and the match field in TableA contains the values:
separated by carriage returns. FileMaker Pro will match any record in TableB where the corresponding match field contains the single value red, green, or blue. However, FileMaker Pro will not return records where the match field contains the value red green blue. The carriage returns tell FileMaker Pro to treat each line as a separate value.
I don't understand your use of the term "bit-wise OR'd value". It looks like you are using a binary number (in decimal form) to pass the on/off status of items - for example, Items 1 and 3 and 4 would be coded as binary 1101 = 2^0 + 2^2 + 2^3 = 1 + 4 + 8 = 13 decimal. IOW, a compacted checkbox.
You then want to decode 13 back to 1¶4¶8 in order to match these items in another table. If so, this is very easy to do using a repeating calculation field =
e = Get ( CalculationRepetitionNumber ) - 1
Mod ( Div ( Extend ( Number ) ; 2^e ) ; 2 ) * 2^e
which can then be used as the matchfield to Items.
I am not aware of any advantages of return-delimited values over a repeating field, and I see nothing in the quoted excerpt to support such claim.
"I am not aware of any advantages of return-delimited values over a repeating field..."
The only significant advantage that I am aware of is that a text field list of return delimited values is flexible in length where the values in a repeating field are static. In other words, if you define a repeating field with 5 repetitions and later discover you need to match 6 or more values you have to update your field definition to include more repetitions, but with a return delimitted list in a text field you can simply add more values to your field without any updates to your field definition.
Relationships are made at developer level.
So, if he can add a value to a list of values, he can even add new repeatitions to the repeating field.
True, but adding an addtional value to the list of matching values does not require adding an additional relationship and is simply an edit operation in the case of a return delimitted list where it is a field definition change in the 2nd case. This is a minor difference in any cases, but is the only significant difference of which I'm aware between the two options.
Context is important. Clearly, the described arrangement requires a prior agreement as to which item maps to which code - which means the total number of possible items is known.
Even so, one could define the repeating field with expansion in mind: repeating fields can have up 32,767 repetitions, so theoretically this "checkbox" could have as many values. In practice, 2^32767 is way beyond what Filemaker's calculation engine can process - no matter which method is used.
I think you gist of what I'm trying to accomplish, comment. I'm reading in data from flat-file/XML sources, and interpreting the 'compacted checkbox'. However, I have previously used the calculated repeating field, in relationships, and found it far more cumbersome. IIRC, speed was the main drawback. Trying to perform finds in the repeating field, or in the related records, was painfully slow. Once I discovered that I could use the return-delimited values to do the same thing, I never went back.
And PhiModJunk is right, adding (or calculating) additional values in a single text field is much more flexible than having to add repetitions - especially if you don't know you need to add more repetitions. And since I like to be economical with my DB structure, I just can't setup 100 repetitions if I'm only going to use 10.
As you may recall from another thread, I have a very low tolerance for drawing conclusions from anecdotal evidence. I am not aware of any such issue - if you have a file that shows it, please post it.
Of course I remember. :smileytongue: I can't offer the databases, but I can tell you that one had over 100,000 records in a single table, with two calculated repeating fields, each based on a related table of about 25,000 records (in two separate databases). The calculated repeating fields were then used in self-relationships to the 100,000 record table. Searches performed in either of the calculated repeating fields, or the related records, would take at least a minute. With multi-key text fields, 10 second searches are the norm.