Is there a more "clever" way... Mulit-Key Relation

[Ocean West]

Ok it is working but it is complex and required a lot of thinking to accomplish.

In my scenario I am using a self-relationship to a single file. The left side of the relationship is a global and the right side is the data.

The field on the left side "KeyA" is a calc field based upon a few other globals.

For sake of this description we will use:

f1 f2 f3 f4 f5 f6 f7

These fields are all numbers and are only a few characters long.

KeyA is a concat of all these fields with a space in between:

The result would be 2 1 2 1 0 0

[Kurt Knippel]

Well start by defining your keys as TEXT. Multi-keys with Number fields start to get funny results as 1 0 2 is NOT a valid number unlike 102. Also indexing is an issue and you may find that most of you key will be not indexed.

With the Date being 7 characters and your values ocupying another 21 values for a total of 29 characters (including the EOL or paragraph character) you can have 2259 lines in your key.

With the date being a constant on each line and having 2 possible options in each of the other keys, you have a maximum of 128 possible combinations.

Notice I said that you only have 2 possible options for each of the other keys? But you say that 0-9 are 10 possible combinations. Well the only values that matter are 0 and everything else, thus 2 combinations as far as the calculation of the key is concearned.

For example:

Date 0 0 0 0 0 0 0

Date f1 0 0 0 0 0 0

Date 0 f2 0 0 0 0 0

Date 0 0 f3 0 0 0 0

...

Date f1 0 0 f4 0 f6 0

...

Date f1 f2 f3 f4 f5 f6 f7

And all the other combinations within.

I have a compound multi-key generator which will generate all the possible combinations for up to 6 variables, plus leading and trailing constants.

[kennedy]

I guess Kurt got it, but I don't understand the problem statement.

Here's what I got...

It appears you are computing from control fields a-g the Key A.

Then you are wanting to use that to lookup the generated Key B from a file where you've pre-generated all 128 possible combinations of fields f1 through f7.

(In your example file you are just using the literal "f1" instead of the real value.)

Each of f1 through f7 are some number that is only a few digits.

So, the longest combo might be 7*6 plus the date long.

But I don't think that's right...

First, 50 characters is obviously not a length issue, given it has spaces every few.

Second, you could just write a calculation to directly compute the KeyB from the control fields and the Fn fields:

KeyB = date & " " & if(a, F1, "0") & " " & if(b, F2, "0") & ... & if(g, F7, "0")

and thus seemingly no need to pre-generate the 128 combinations and then do the lookup.

Since that calculation is clearly obvious to you based on the example file, I obviously do not get at all the problem statement. Sorry if I am being dense.

[Ocean West]

Kurt you are right Both keys need to be text.

And you are also right it took 128 keys to generate every possible combination.

I was just curious if there was a simpler way to generate this key.

(PS it is done and is working, just looking for ways to optmize code)

Enclosed is the calculation for KeyB

key.txt

[kennedy]

Not sure if this is an "optimization" or not... its probably slower than the giant hand-written concat... but here's a way you can compute the concat. In fact, all the data is computed... I just had to hit Cmd-N 128 times... I guess I could have written a script to call New Record 128 times.

KeyGenerator.fp5.zip

[Kurt Knippel]

Nope, in essense you have the correct solution.