Seek help to find array position number in a array loop

Haavard_Hjorteland
2 yrs ago
7replies

Hello,

This is my first post, so please bear with me if the formatting and my wording dont make senese....

I am creating a genealogy database just for the fun of it, and I have a detail in a desendant list formula I just can't get right. Here is some background info.

I have many tables, in this case the important are:

Table 'Pesoner' (Persons) , records of ancestors, and where I run the formula from the selected Record, listing all the persons decendants.

Linked table 'Ekteskap' (Marriage) is records of all marriages , where EK (Ektemann) = Husband and HU (Hustru) = Wife. From the Personer table I can find a persons parents by FO.EK and FO.HU (FO = Foreldre = Parents)

In every 'Personer' record I have the 'BA' formula running, witch is a list of all the persons children , this is children from all the persons marriages if applicable. So, a husbands BA list may vary from a wife BA list if several marriages is involved.

So , BN is all the selected root persons children, BN.BN is all grandchildren, BN.BN.BN all great grandchildren etc. I am bundling together all BA decendants in the 'bA' array. Since husbad/wife sometimes was related, their descendats may be listed muliple times further down the list. To deal with this I have an optional allmost identical formula using: let bA1_2 := array(unique(BN)[Id > ""], unique(BN.BN)[Id > ""]); etc.

This examle formula below only lists 5 generations, but I have similar for up to 11 generations.

For this to be readable and meaningful it is important to easily find the children of a person lower in the list by using the persons white number (+ G/Generation nr) in eighter the blue F (Father) or pink M (mother). And revere.

As example, in the "OK" image nr. 19 G4 (F5 M) Ola Olson we find that his father is nr. 5 at position 5.G3 Ola Pederson.

MY PROBLEM IS:

I havent figured out how to find/calculate the index number back to a persons father or mother in a efficient way. The OK image is from a similar formula where I also in paralell have a button running pretty much the same loop , and on click it is on the fly generating the c counternumber in a text field 'FOGEN' for all the records. But this is a very slow approach, in a 11 GEN list with many decendants it may take 10-20 seconds, and I have to do a reverse loop and delete all 'FOGEN' records afterward when I want to change from e.g 11 GEN list to 9 GEN list

part button formula:

for p from p to m do
           let x := slice(bA, p, p + 1);
   c := c + 1;
               x.(FOGEN := c )

and I am reading this back in the "OK" formula using x.FO,EK.FOGEN and x.FO.HU.FOGEN , but this is SLOW.

Below is the script for the'NOT_OK' image. This runs super fast, but it is not giving me the father or mother "index" position number"

I AM able to find the x father or mother ID's in the array by this:

let fa := first(bA[Id = x.FO.EK.Id]);
let m := first(bA[Id = x.FO.HU.Id]);

BUT: This is not what I need, I need to be able to refere to the parents s counter number ! Just like in the unefficient "OK" script using a button.

Hope this made sense, and somone have a solotion

"Not OK" formula:

do as server
 if Test11on = 1 and 'Ikke gjenta pers' = 0 then
 let bA1_2 := array(BN[Id > ""], BN.BN[Id > ""]);
 let bA3_4 := array(BN.BN.BN[Id > ""], BN.BN.BN.BN[Id > ""]);
 let bA := array(bA1_2, bA3_4);
 let m := cnt(bA);
 let G1 := cnt(BN);
 let G2 := cnt(BN.BN);
 let G3 := cnt(BN.BN.BN);
 let G4 := cnt(BN.BN.BN.BN);
 let p := 0;
 let c := 0;
 let x := 0;
 for p from p to m do
 let x := slice(bA, p, p + 1);
 if c < G1 then
 c := c + 1;
 let fa := first(bA[Id = x.FO.EK.Id]);
 let mo := first(bA[Id = x.FO.HU.Id]);
 let gn := "G2";
 sort(html("<h1 style=font-size:14px>" + c + "." + gn + "" + " (F" + fa + "" + " M" + mo + ") " + "" + x.'Barn_nr.' + " " + "" + " " + substring(text(x.'Kjønn'), 0, 1) + " " + "" + x.Fornavn + " " + x.Farsnavn + " " + x.Etternavn + "" + " f. " + x.'Fødested'.Sted_prop + " " + x.'Fødested'.Stedsnavn + " " + "" + " " + x.'Fødeår' + "" + " " + x.Dod_stat + " " + x.'Dødssted'.Sted_prop + " " + x.'Dødssted'.Stedsnavn + " " + "" + " " + x.'Dødsår' + "" + " , " + x.Alder + " " + "" + " , " + x.ANT_BARN + " barn " + "" + " |FOR: " + "" + x.FO.EK.Fornavn + " " + x.FO.EK.Farsnavn + " " + x.FO.EK.Etternavn + " og " + x.FO.HU.Fornavn + " " + x.FO.HU.Farsnavn + " " + x.FO.HU.Etternavn + " " + " " + "" + " |EF1: " + "" + x.EF1.Fornavn + " " + x.EF1.Farsnavn + " " + x.EF1.Etternavn + "" + " EF2: " + "" + x.EF2.Fornavn + " " + " " + x.EF2.Farsnavn + " " + x.EF2.Etternavn + "" + " "))
 else
 if c < G1 + G2 then
 c := c + 1;
 let fa := first(bA[Id = x.FO.EK.Id]);
 let mo := first(bA[Id = x.FO.HU.Id]);
 let gn := "G3";
 sort(html("<h1 style=font-size:14px>" + c + "." + gn + "" + " (F" + fa + "" + " M" + mo + ") " + "" + x.'Barn_nr.' + " " + "" + " " + substring(text(x.'Kjønn'), 0, 1) + " " + "" + x.Fornavn + " " + x.Farsnavn + " " + x.Etternavn + "" + " f. " + x.'Fødested'.Sted_prop + " " + x.'Fødested'.Stedsnavn + " " + "" + " " + x.'Fødeår' + "" + " " + x.Dod_stat + " " + x.'Dødssted'.Sted_prop + " " + x.'Dødssted'.Stedsnavn + " " + "" + " " + x.'Dødsår' + "" + " , " + x.Alder + " " + "" + " , " + x.ANT_BARN + " barn " + "" + " |FOR: " + "" + x.FO.EK.Fornavn + " " + x.FO.EK.Farsnavn + " " + x.FO.EK.Etternavn + " og " + x.FO.HU.Fornavn + " " + x.FO.HU.Farsnavn + " " + x.FO.HU.Etternavn + " " + " " + "" + " |EF1: " + "" + x.EF1.Fornavn + " " + x.EF1.Farsnavn + " " + x.EF1.Etternavn + "" + " EF2: " + "" + x.EF2.Fornavn + " " + " " + x.EF2.Farsnavn + " " + x.EF2.Etternavn + "" + " "))
 else
 if c < G1 + G2 + G3 then
 c := c + 1;
 let fa := first(bA[Id = x.FO.EK.Id]);
 let mo := first(bA[Id = x.FO.HU.Id]);
 let gn := "G4";
 sort(html("<h1 style=font-size:14px>" + c + "." + gn + "" + " (F" + fa + "" + " M" + mo + ") " + "" + x.'Barn_nr.' + " " + "" + " " + substring(text(x.'Kjønn'), 0, 1) + " " + "" + x.Fornavn + " " + x.Farsnavn + " " + x.Etternavn + "" + " f. " + x.'Fødested'.Sted_prop + " " + x.'Fødested'.Stedsnavn + " " + "" + " " + x.'Fødeår' + "" + " " + x.Dod_stat + " " + x.'Dødssted'.Sted_prop + " " + x.'Dødssted'.Stedsnavn + " " + "" + " " + x.'Dødsår' + "" + " , " + x.Alder + " " + "" + " , " + x.ANT_BARN + " barn " + "" + " |FOR: " + "" + x.FO.EK.Fornavn + " " + x.FO.EK.Farsnavn + " " + x.FO.EK.Etternavn + " og " + x.FO.HU.Fornavn + " " + x.FO.HU.Farsnavn + " " + x.FO.HU.Etternavn + " " + " " + "" + " |EF1: " + "" + x.EF1.Fornavn + " " + x.EF1.Farsnavn + " " + x.EF1.Etternavn + "" + " EF2: " + "" + x.EF2.Fornavn + " " + " " + x.EF2.Farsnavn + " " + x.EF2.Etternavn + "" + " "))
 else
 if c < G1 + G2 + G3 + G4 then
 c := c + 1;
 let fa := first(bA[Id = x.FO.EK.Id]);
 let mo := first(bA[Id = x.FO.HU.Id]);
 let gn := "G5";
 sort(html("<h1 style=font-size:14px>" + c + "." + gn + "" + " (F" + fa + "" + " M" + mo + ") " + "" + x.'Barn_nr.' + " " + "" + " " + substring(text(x.'Kjønn'), 0, 1) + " " + "" + x.Fornavn + " " + x.Farsnavn + " " + x.Etternavn + "" + " f. " + x.'Fødested'.Sted_prop + " " + x.'Fødested'.Stedsnavn + " " + "" + " " + x.'Fødeår' + "" + " " + x.Dod_stat + " " + x.'Dødssted'.Sted_prop + " " + x.'Dødssted'.Stedsnavn + " " + "" + " " + x.'Dødsår' + "" + " , " + x.Alder + " " + "" + " , " + x.ANT_BARN + " barn " + "" + " |FOR: " + "" + x.FO.EK.Fornavn + " " + x.FO.EK.Farsnavn + " " + x.FO.EK.Etternavn + " og " + x.FO.HU.Fornavn + " " + x.FO.HU.Farsnavn + " " + x.FO.HU.Etternavn + " " + " " + "" + " |EF1: " + "" + x.EF1.Fornavn + " " + x.EF1.Farsnavn + " " + x.EF1.Etternavn + "" + " EF2: " + "" + x.EF2.Fornavn + " " + " " + x.EF2.Farsnavn + " " + x.EF2.Etternavn + "" + " "))
 end
 end
 end
 end
 end
 end
end

Scripting and Functions

- Haavard_Hjorteland
- 2 yrs ago
- Reported - view
Hi again,

I have almost made this formula work as I want, but not quite 100%, so I hope someone can help me.

I found something relevant for me in the recent "Character Occurrences count" thread here:

Character Occurrences Count - Get help - Ninox Community

I could not fully understand how to implement Alain's solution using replacex :

length(replacex(myString, "[^" + myLetter + "]", "g", ""))

So i have tried John Halls "non-loop" solution, modified, and it is working ca. 95 % of the time, I like the idea to avoid loops within loops.

let found := count(split("*"+mystring+"*","e"))-1;

But this is with issues in some occasions. I have marked yellow my updated code. The idea is to count the number of ',' commas in the concat string from zero to the fathers or mothers position.

let bAc := concat(bA.Id); <<turns the bA array into a comma seperated string

let fa := first(bA[Id = x.FO.EK.Id]); << find x's Father Id in the bA array
let fac := index(bAc, text(fa)); << find the fathers index position in the concat string
let facp := text(substring(text(bAc), 0, fac)); << reduce the string from zero to fathers position
let fnr := count(split("*" + text(facp) + "*", ",")); << count the "," commas = same as x. fathers c. counter number in the list. And the same approach to find the mothers position.

The problem with this, as pointed out by Alain, is that it sometimes fails if similar numbers follow each other. As example, se attached image.

My daughter Siri is number 44.G5, and her F2 father number points to number 2 .G2 Hans Oveson (witch was my grandads brother and not me :-), btw he is hard to beat with 105 years at death.

I myself are number 30.G4, and this seems to fail since my ID is 70 (marked green), but nr2 Hans's ID id is earlier in the string, and his ID is 4070 !

So again, hope for some help. It is just a matter of counting commas in a string....... I would prefer a solution without creating loops within the main loop, I am not sure how to implement that.

part of updated formula, works ca. 95 % of the time:

do as server
 if Test11on = 1 and 'Ikke gjenta pers' = 0 then
 let bA1_2 := array(BN[Id > ""], BN.BN[Id > ""]);
 let bA3_4 := array(BN.BN.BN[Id > ""], BN.BN.BN.BN[Id > ""]);
 let bA := array(bA1_2, bA3_4);
 let bAc := concat(bA.Id);
 let m := cnt(bA);
 let G1 := cnt(BN);
 let G2 := cnt(BN.BN);
 let G3 := cnt(BN.BN.BN);
 let G4 := cnt(BN.BN.BN.BN);
 let p := 0;
 let c := 0;
 let x := 0;
 for p from p to m do
 let x := slice(bA, p, p + 1);
 if c < G1 then << I don't need the fathers in the first generation
 c := c + 1;
 let fa := first(bA[Id = x.FO.EK.Id]);
 let mo := first(bA[Id = x.FO.HU.Id]);
 let gn := "G2";
 sort(html("<h1 style=font-size:14px>" + c + "." + gn + "" + " (F" + fa + "" + " M" + mo + ") " + "" + x.'Barn_nr.' + " " + "" + " " + substring(text(x.'Kjønn'), 0, 1) + " " + "" + x.Fornavn + " " + x.Farsnavn + " " + x.Etternavn + "" + " f. " + x.'Fødested'.Sted_prop + " " + x.'Fødested'.Stedsnavn + " " + "" + " " + x.'Fødeår' + "" + " " + x.Dod_stat + " " + x.'Dødssted'.Sted_prop + " " + x.'Dødssted'.Stedsnavn + " " + "" + " " + x.'Dødsår' + "" + " , " + x.Alder + " " + "" + " , " + x.ANT_BARN + " barn " + "" + " |FOR: " + "" + x.FO.EK.Fornavn + " " + x.FO.EK.Farsnavn + " " + x.FO.EK.Etternavn + " og " + x.FO.HU.Fornavn + " " + x.FO.HU.Farsnavn + " " + x.FO.HU.Etternavn + " " + " " + "" + " |EF1: " + "" + x.EF1.Fornavn + " " + x.EF1.Farsnavn + " " + x.EF1.Etternavn + "" + " EF2: " + "" + x.EF2.Fornavn + " " + " " + x.EF2.Farsnavn + " " + x.EF2.Etternavn + "" + " " + x.Id + " "))
 else
 if c < G1 + G2 then
 c := c + 1;
 let fa := first(bA[Id = x.FO.EK.Id]);
 let fac := index(bAc, text(fa));
 let facp := text(substring(text(bAc), 0, fac));
 let fnr := count(split("*" + text(facp) + "*", ","));
 let mo := first(bA[Id = x.FO.HU.Id]);
 let moc := index(bAc, text(mo));
 let mocp := text(substring(text(bAc), 0, moc));
 let mnr := count(split("*" + text(mocp) + "*", ","));
 let gn := "G3";
 sort(html("<h1 style=font-size:14px>" + c + "." + gn + "" + " (F" + fnr + "" + " M" + mnr + ") " + "" + x.'Barn_nr.' + " " + "" + " " + substring(text(x.'Kjønn'), 0, 1) + " " + "" + x.Fornavn + " " + x.Farsnavn + " " + x.Etternavn + "" + " f. " + x.'Fødested'.Sted_prop + " " + x.'Fødested'.Stedsnavn + " " + "" + " " + x.'Fødeår' + "" + " " + x.Dod_stat + " " + x.'Dødssted'.Sted_prop + " " + x.'Dødssted'.Stedsnavn + " " + "" + " " + x.'Dødsår' + "" + " , " + x.Alder + " " + "" + " , " + x.ANT_BARN + " barn " + "" + " |FOR: " + "" + x.FO.EK.Fornavn + " " + x.FO.EK.Farsnavn + " " + x.FO.EK.Etternavn + " og " + x.FO.HU.Fornavn + " " + x.FO.HU.Farsnavn + " " + x.FO.HU.Etternavn + " " + " " + "" + " |EF1: " + "" + x.EF1.Fornavn + " " + x.EF1.Farsnavn + " " + x.EF1.Etternavn + "" + " EF2: " + "" + x.EF2.Fornavn + " " + " " + x.EF2.Farsnavn + " " + x.EF2.Etternavn + "" + " "))
 else
 if c < G1 + G2 + G3 then

formula countionues in nested if/then, since I have not found a better soluution to calculate the G.(eneration) numbers
- Gianluca
- 2 yrs ago
- Reported - view
Hi Haavard

sorry...I'm not a real expert and I can't understand your huge script so bear with me for the silly question but: you say you need to count commas in a string? So maybe you can create this function

function countLetters(myString : text,myLetter : text) do length(replacex(myString, "[^" + myLetter + "]", "g", "")) end; let found := countLetters(FIELDTHATCONTAINSCOMMAS, ","); found

Copy it exactly as it is and just change FIELDTHATCONTAINSCOMMAS with the field you want to analyze.

It counts the right amounts of commas in my tryouts. If that's your need, it should work for you too.
- Gianluca
  
  2 yrs ago
  
  Reported - view
  this is the same function that Lars have suggested in the Character occurrences count post
- Haavard_Hjorteland
- 2 yrs ago
- Reported - view
Thank you Giannluca,

I have implemented this now:

function countLetters(myString : text,myLetter : text) do
           length(replacex(myString, "[^" + myLetter + "]", "g", ""))
end;

and inside the  loop:

                   let fa := first(bA[Id = x.FO.EK.Id]);
                   let fac := index(bAc, text(fa));
                   let facp := substring(bAc, 0, fac);
                   let fnr := countLetters(text(facp), ",") + 1;

However, it gives med the exact occational error when the ID I'm looking for ends with the same two digits. So I have "debugged" by displaying the fa (Id number) and fac (index position)

I see that THÌS is my problem: let fac := index(bAc, text(fa));

So the code you gave me is OK, and I guess my previous "let fnr := count(split("*" + text(facp) + "*", ","));" was OK as well.

As seen in yet another image, the children nr. 15, 16 etc shows the correct father ID (38), but the wrong fac/index number! It shows index start position 12 insead of 50'ish as it should be.

I need the index position right to count the commas, but it seems like e.g searching for 38 as is correct ID in this case, the index command stops and find ID 138 witch is earlyer in the string.

hmmm, is this error in my code, or a bug I wonder ?
- Haavard_Hjorteland
- 2 yrs ago
- Reported - view
FYI, I resolved this by creating a new fx field in the Personer records enclocing the ID's like this: |655|

Now the index search works OK , and all the fathers and mothers numbers is correct :-)
- Alain_Fontaine
- 2 yrs ago
- Reported - view
I was about to propose another solution:

let bAc := "," + concat(bA.Id) + ","; … let fac := index(bAc, "," + text(fa) + ",");

It works on the same principle, which to "guard" the strings to be compared in order to avoid partial matches.

About comma counting: il you always count commas, and nothing but commas, it is overkill to define a function that can count anything. The number of commas in string "x" is simply:

length(replacex(x, "[^,]", "g", ""))

It works by removing all the characters that are not commas, and counting what remains, and gives the correct result even if there are suites of commas in the string.
- Haavard_Hjorteland
- 2 yrs ago
- Reported - view
Thank you Alain,

length(replacex(x, "[^,]", "g", "")) worked perfectly, and I have removed the function. But that one will be handy for other scripts. I also tried something like your "," concat "," suggestion before crating the new |Id| field, but I guess I had the syntax wrong.

Content aside

2 yrs agoLast active
7Replies
278Views
3 Following

Seek help to find array position number in a array loop

7 replies

Content aside

Overview