Complex custom function needed

[LingoJango]

Hi, I tried asking for help on this a while ago but no luck. Maybe this time...

 

I have a contacts table and an interpreter qualifications table.

 

Interpreter qualifications include language as ISO code and qualification as A, B or C.

lg  qual
--  ----
en  A
fr  B
de  C
nl  B

 

From this I would like to obtain this output:

 

A: en; B: fr, nl; C: de

 

I have set up a sorted relationship (reverse sort, by a value list of C¶B¶A). So I know I can work with List to get

 

en

fr

nl

de

 

and

 

A

B

B

C

 

and I also have a field I use for searches:

 

en - A

fr - B

nl - B

de - C

 

but I don't know how to turn any of this into what I want. I'm almost certain I need a recursive function, but I'm still getting lost in recursive logic.

[Weetbicks]

I'm trying to come up with a custom function to do this for you, and as you said it is reasonably complex.

 

So in the mean time there is another way to do it using calculation fields and building relationships to obtain the result you want.

 

 

Okay, so some initial setup for the example, we'll call the main table "Interpreter" , this is where the calculation for the result you want is going to be, and also other calculations we'll create which will help build it.

 

Note: I'm not testing this as I'm writing it so if you implement verbatim it may or may not work I can't guarantee, but the general idea of how I think its going to work is there, I'll let you tweak it to get it exactly how you want...

 

---------

 

Okay, first relationship is from Interpreter to another table occurrence of interpreter (called InterpreterUnique). The relationship will be:

 

Qualification = Qualification

 

What this does is for every interpreter record, relates thru to all other interpreter records with the same qualification. In your original example that means all records will relate to themselves, and in the case of "B" it will relate to 2 records.

 

Setup a calculation on the interpreter table called "FirstQualification":

 

if ( RecordID = InterpreterUnique::RecordID ; 1 ; 0 )

 

This basically flags each interpreter record with 1 or 0, it also means only one of the B's will be flagged a 1, the other will be 0. If there are 100 "C"s, only the FIRST one will be 1, the rest are 0. Also note that RecordID needs to be a unique identifier ie a primary key.

 

---------

 

Okay so now you have tagged your records with either 1 or 0. You need another calculation which we'll call "QualificationLine" , this will contain one of the lines of your final list, for a particular qualification letter, ie:

 

B: fr, nl

 

 

The calc looks as follows:

 

if ( FirstQualification = 1 ;

Qualification & ": " & Substitute ( List ( InterpreterUnique::language ) ; "¶" ; ", " ) ; ""

)

 

What this does: If the recrod is flagged as unique (ie 1) , we build the line by specifying the qualification letter followed by the semicolon. We then list all the qualification languages using the relationship created earlier. Finally we substitute all returns in the list for a comma and a space. Hopefully the end result (ie for B) would be:

 

B: fr, nl

 

REMBMER: This calculation only calculates when the unique field is 1. This is important, for all other non-unique records the calc is empty.

 

-------

 

 

The final step is the last calc. Create a new relationship to another instance of Interpreter, you can make this relationship cardinal (cross product X) so it relates to ALL interpreter records. The table occurrence is called "InterpreterFinalList"

 

Create 1 more calc for the completed list, it is simply:

 

List ( InterpreterFinalList::QualificationLine )

 

and bingo yer done. The list function won't list all the QualificationLine calcs which are empty (which are all the ones where the unique flag is 0). And since there is only one line per qualification letter (since it only gets evaluated when the unique flag is 1), you end up with a list of 1 line per qualification letter.

 

-------

 

 

OH god I hope this makes sense lol! Have a go and see how you get on. I'm fairly confident this will give you what you want, you may need to tweak it a little bit. And of course a custom function is going to be far more elegant, but if you only need to generate this calc once in one place this could suffice.

 

Final count is 3 calcs and 3 table occurrences.

 

good luck!

[CobaltSky]

...I'm almost certain I need a recursive function...

Hi LingoJango,

There's a fair bit of information you haven't given us. For example, you haven't said whether you still want the A, B or C qualifications to appear in the output string if there are no entries (related records) against them for a given contact - nor have you said whether there are ever unqualified language entries or quals with null languages (and if so, in either case, how you'd want to handle them).

 

Notwithstanding that, the recursive custom function you're looking for is probably something along the lines of the following:

 

// SYNTAX: QualSummary ( Language ; Qualification )

 

Case(

IsEmpty($QSnx);

Let([

$QSvs = Filter("ABC"; List(Qualification)) & "D";

$QSnx = Left($QSvs; 1) & "1";

$QSmx = Max(Count(Language); Count(Qualification))];

QualSummary(Language; Qualification));

not ($QSnx = "D1");

Let([

Q = Filter($QSnx; "ABC");

N = GetAsNumber($QSnx);

vQ = GetNthRecord(Qualification; N);

vL = GetNthRecord(Language; N);

xQ = Middle($QSvs; Position($QSvs; Q; 1; 1) + 1; 1) & "1";

$QSnx = If(N ≥ $QSmx; xQ; SerialIncrement($QSnx; 1));

Pfx = If(N = 1; "; " & Q & ": ");

Sfx = If(vQ = Q and Length(vL); ", " & vL);

$QSst = $QSst & Pfx & Sfx];

QualSummary(Language; Qualification));

Let([

R1 = $QSst;

$QSnx = ""; $QSmx = ""; $QSvs = ""; $QSst = "";

R2 = LeftWords(R1; WordCount(R1))];

Substitute(R2; ": , "; ": "))

)

 

For this example, I've assumed you'd want nulls or absent qual entries omitted from the resulting summary string. (See attached example file, tested in v8.5 and v9).

 

BTW, this method doesn't require any special relationships or relationship sorting (unless you want the languages within each qualification group to be presented in a given order in the output string).

 

HTH

 

.

[LingoJango]

That's awesome, Ray! I'll spend a few hours getting my mind around this.

 

Your assumptions are all correct - nulls are to be ignored.

 

And I also have to thank Weetbix (almost named after my favorite cereal!) for reminding me of the clever uses of relationships, even though I'll be using the custom function instead this time.