Loop+counters driving me mad

[Ugo DI LUCA]

Hi Forum,

This may be too much to ask, or as already been adressed, somewhere burried here on the thousands of "Multi-keys" threads....

I have 7 fields from Field A to Field G

Each of them hold a different number of key values separated by a "

[djgogi]

Hi Ugo,

bad news, you'll reach the 64K limit pretty soon:

The (max) num of combinations (given the limits of 7 fields with up to 3 items) is

21

Sum ( 21 i )=(21 0) + (21 1) + (21 2)+ .....+(21 19) +(21 20) +(21 21)

i=0

=1+21+ 21*20/2! +21*20*19/3! +...+21*20*....*4*3/19!+21*20*...*2/20!+1)

where n!=n*(n-1)*(n-2)*...*2*1

Dj

[Ugo DI LUCA]

Ciao Goran,

I had the feeling I would have had an answer from the Multikey master

Given that my maths skills are way lower than yours, I'm not sure wether your calc incorporate or not reversed keying.

The loop in my situation would always set A&B&C&D&E&F, moving only the "occurence" inside each field.

With some "risks", I assume your evaluation is therefore way up. Sorry I should have told it before.

A 3 fields (max 3 values) would give a (3*3)*3

A 4 fields (max3 values) would give a (4*3)*4

...

A 7 fields (max3 values) would give a (7*3)*7 = 441 lines

Am I correct ?

Nevertheless, I'm still stuck with this loop, even with a 4 key fields sampler (that I assume won't break the 64K limitation)

Here's attached a test file.

Anyone wiling to debug it ?

I have the feeling the errors are coming from my c_occuranceA,B,C,D fields which are dependant on the counters.

Her's some insight on the logic I used for the script, as it may be confusing to go into the attachment without "Why,how,for What"

Here are the counters set in multiple loops:

Consider A,B,C,D the FieldNames and the number next to it the occurence to be extracted.

The first 2 counters are controling the "locking" on the starting field and the "movement" in that field.

Starts A-1, then A-2,A-3,B-1,B-2,B-3,C-1,C-2,C-3,D-1,D-2,D-3,End Loop.

The next 2 counters are controling the movement on the next field and stepping "movement" in that next fied.

Starts A-1-B-1, then

A1-B2,

A1-B3,

A1-C1,

A1-C2,

...,

D3,D2,

End Loop.

The next 2 counters are contriling the movement on all other fields in the loop,

Starts A1-B1-C1, then

A1-B1-C2/D1,

A1-B1-C3/D1,

A1-B1-C1/D1,

A1-B1-C1/D2,

A1-B1-C3/D3,

....

End Loop.

Thanks again DJ as usual.

And thanks to anyone willing to help on this looping script.

Loop.fp5.zip

[djgogi]

Just in case you need something simpler, like multi key with "fixed" number of elements (in this case every line contains up to 7 elements), here is the example

Dj

Ugo.fp5.zip

[djgogi]

Hi ugo,

I posted reply before refreshing the screen.

So the file I've uploaded is what you'were looking for.

The previous formula is entire multi key (including (single field) internal combinations)

In case you need only one distinct value from every field then max number of combinations is :

numOfElements^numOfields (=2187)

BTW the text in file saying "the number of combinations: xxxx" is wrong (it is the word count, oops)

Dj

[Ugo DI LUCA]

Mamma mia !

You R a Killer, Goran

I don't actually have fixed lenght, but I can live with this for sure.

I'll have a subscript delete each linekey lower than 14 characters, so that I have my Multikey ready for what I was into.

I just love you and Italy.

Now I'll explore your sampler again. Thanks.

[Ugo DI LUCA]

Goran,

Forget about my last script to delete those non 14 lenght linekeys....

Stupid thing. I just was too much excited and couldn't wait to see the sampler in depth before thanking you.

All is working as a treat.

Thanks.

[djgogi]

While we are on argument,

When setting right keys for multikey relationships using scripts the max size of key is not limited to 64 000 chars but to 64 000 000 ( 64 millions)

Of course it is a time consuming process.

The attached example show how to extend the key to up to 320 000 chars (I was to lazy to extend it more )

Added:

Don't download this attachement. The corrected version is few posts bellow

Dj

There is potentially very nasty bug in first if statement of "SetNext" script:

Change ">64000" to ">= 64000"

The entire condition should state:

(Length(GetRepetition(result, gNext))+Length(MiddleWords(f1, gCounter1, 1) & " " & MiddleWords(f2, gCounter2, 1)& " " & MiddleWords(f3, gCounter3, 1)& " " & MiddleWords(f4, gCounter4, 1)& " " & MiddleWords(f5, gCounter5, 1) & " " & MiddleWords(f6, gCounter6, 1) & " " & MiddleWords(f7, gCounter7, 1))) ? 64000

UgoExtend64Klimit.fp5.zip

[Ugo DI LUCA]

Hi Dj,

"I was to lazy to extend it more"

You're so kind, and I really doubt you are really that lazy guy always lezarding on Pisa's plazas.

Actually, I was quite at this point for another project, and this comment may be really useful.

At the moment, the files (which a rough demo-version (CheckboxFinds ) was uploaded here some time ago) allows searches involving checkboxes for "Exact/Or/And" matches. It is currently limited to 13 checkboxes, while my real need was of 18.

In order to by-pass FM's limitation, I used a workaround (not in the posted demo) in the final files.

I used 2 separate Multi-keys for the right side, and 2 others for the left side. A script generates 2 lists of Related Ids issued from the 2 left MultiKeys matches, and a sub-script extracts in a loop each Related ID's from g_Key1, compares it with the values of g_Key2, and vice-versa.

Because I explained the limits of the CheckBocFinds sampler, I was pointed to this repeating field scenario just the other day.

I imagined it running just as it is right now, using a second rep instead of a second field for the right side.

With such a brilliant demonstration, I wonder now how this Multivalued key could be running in the case of a real MultiKey ? Could I join my left key in only one MultiLine and make it relate to that new Repeating key ?

[djgogi]

Actually there were 2 bugs:

There is potentially very nasty bug in first if statement of "SetNext" script:

Change ">64000" to ">= 64000"

The entire condition should state:

(Length(GetRepetition(result, gNext))+Length(MiddleWords(f1, gCounter1, 1) & " " & MiddleWords(f2, gCounter2, 1)& " " & MiddleWords(f3, gCounter3, 1)& " " & MiddleWords(f4, gCounter4, 1)& " " & MiddleWords(f5, gCounter5, 1) & " " & MiddleWords(f6, gCounter6, 1) & " " & MiddleWords(f7, gCounter7, 1))) ? 64000

Also I have put the subscript in wrong place inside main script.

It should come before Set gCounter step.

Anyway I'm reposting the corrected version

IMPORTANT

If you plan to use this method don't perform script on index-enabled field (speed performance).

Instead transfer the content of the result field to indexable version of it.

UgoExtend64Klimit.fp5.zip

[djgogi]

The problem with repeated fields as left key is that they will break the relationship on first non valid value (for which non exists related record) after which the rel won't work for successive repetitions.

(However this is not the true if repeated left key is used as lookup key for another repeated field)

Dj

[djgogi]

Well I was not very clear here:

Actually only the first repetition will be used for relationship and only the first related record (if any) for every repetition for repeated look up fields

Dj

[Ugo DI LUCA]

Well yes I know that.

The current setting is :

Right Multikey1 (scripted Multikey with Value 1 to 10)

Right Multikey2 (scripted Multikey with Value 11 to 20)

Left Multikey1 (scripted on 10 checkboxes (1to10)

Left Multikey2 (scripted on 10 checkboxes (11 to 20)

The scripts extracts for left side

t_ValueListItems1

t_ValuelistItems2

Then parse each "value list of IDs" and keep in a final t_key those which are in both t_ValueListItemsKeys.

Then t_key relates to IDs.

As it is right now, used for finds, the left key will never exceed the 640K. But the right key could.

What I was wondering is to use only one key for the left side (instead of 2) and makes it relate to a Multivalued Multikey formated as your other "standard" MultiKey, that is

A

AB

AC

ABC

...

Also,

"If you plan to use this method don't perform script on index-enabled field (speed performance).

Instead transfer the content of the result field to indexable version of it."

Could you extend a little on this. There are no calc in your method, just a scripted Multi-key.

Why would it make it slower to work on that scripted key rather than on another set with SetField(scriptedKey,NewKey).

Am I missing something here Goran ?

Thank you.

[djgogi]

And yes if the left key is the standard multi line key then it will relate to any repetition (when match exists, of course) of the right key.

Dj

[Ugo DI LUCA]

Goran,

Inimitabile, inarrestabile.

Just to tell. I'm no more talking about the initial part of this thread. This is over, done, success, miracle, smile....

I'm just taking profit of your time this "night"....

[djgogi]

Ah yes I forgot to mention ?! I've added this subscript in last file (bugs free, I hope) I've uploaded.

The reason is simple:

If you run the script on field that has to be indexed (like right keys ) then the index will be rebuild on each iteration.

When you postpone the indexing as last step when all loops have been terminated the speed difference is enormous (exponential)

Try it in both case

Dj

[Ugo DI LUCA]

OK, I get it.

You were talking of the loop script, while I thought you were pointing out an issue in using the final result in an ulterior script process.

Thanks for this update you "lazy" guy

[djgogi]

Vado a dormire!

Ciao

Dj

[Ugo DI LUCA]

Anch'io...

May be I'd find what's your hidden secret about this "empty" field for the look-up which stays always empty.

Miracles may happen at sleep.

Buona notte. Grazie ancora per l'aiuto e la "piccola" lezione.

[djgogi]

It's a Top Secret

I told you I'm lazy, suppose that you have repeated field with n repetitions, now each time you need to reset the field to default values (usually empty) you have only 2 options:

1) Create an script with n SetField Steps one for each repetition (static, if you change the number of reps you need to adjust the script)

or

2)Relook up it from an already empty field (dynamic, you don't have to change anything if the dimension of field changes)

I've opted for the second one

[Ugo DI LUCA]

Goran,

Yes, I felt a bit supid when I discovered the look-up was intended to reset the "result". I was already that busy with your first "classic" index Loop script, where you invoved a SetField[result,""] because of non-repetitive format, I think I just wondered what this new feature was adding.

I had a terrible time this morning when the clock rang.

I spent a great part of last night dissecting your sampler, testing it over and over.

My eyes wide opened, I felt as a total newbie while looking at the script printed sheet.

I introduced a Pause and a Message Box (Dj is doing this, that, all this, all that,...) into each loop and studied the process step by step.

Not a great night, but surely a great time !

1) How you set the counter is just amazingly "non-intuitive" and powerful

2) How you control each loop based on the occurances of "

[Ugo DI LUCA]

Hi Dj,

A quick test with 20 fields populated with a single letter gave the result you probably expected. It's impossible.

I could set the one to three word in a single repetition, but the whole theory blew in parts when trying to index a 4 words key.

Hmm.. quick was a bit of an exageration as I exactly done the contrary of what you had advised ("When you postpone the indexing as last step when all loops have been terminated the speed difference is enormous (exponential)").

Curiously though, the Result field stopped incrementing at Lenght 27375, that is to say 13245 words or 3459 lines. I can see the counter increase but the key isn't moving from last extracted concanation, "DGJO".

I wish I could at least have seen "DJ"

I have the test files available if you wish to have a look.

Thanks again. Great lessons I had anyway, and the main issue of this thread was beautifully solved. So all the rest is bonus and I may add a closure to this post.

Keep us the good work, take care, and please, keep being that lazy...