Dynamic Option Field Lookup

I have been working on an integration system of late, the idea behind the system, is to allow the user to import excel worksheets, and to allow them to dynamically setup how this is done. Instead of having to code a new import function, over the Excel Buffer table, every time a new sheet format arrives, the user can now import an example of it and then using that as a template, create a mapping table for where the result should land.

To accomplish this, I created two template/settings tables. This gives the system the option of not only handling single tables (eg. Customer), but linked tables too (eg. Document Header and Lines).

The next issue was that I needed to be able to accept fixed values. Which is fine for most variables, but for Option fields, I wanted to enable lookups. This isn’t normally an issue, an Option field will, per standard become a dropdown box and populate itself. However in my case, because I am using a single text field which accepts all fixed values, this is not possible.

So how does one create a Lookup for a FieldRec of type Option?

Adding the following code to the Value fields OnLookup Trigger,  where “TableNo.” and “FieldNo.” are references to the requested field and “Fixed Value” is a boolean indication that this is a fixed value setting.

IF ("Table No." <> 0) AND ("Field No." <> 0) AND ("Fixed Value") THEN BEGIN
  varRecRef.OPEN("Table No.");
  varFieldRef := varRecRef.FIELD("Field No.");
  IF FORMAT(varFieldRef.TYPE) = 'Option' THEN BEGIN
    Value := SELECTSTR(STRMENU(varFieldRef.OPTIONSTRING), varFieldRef.OPTIONSTRING);
  END;
END;

You get:

 

And yes, it works, but I wanted a “proper” lookup, something like this:

To do this isn’t that much more work, but it does require a new Form object, and a little more code.

The following code needs to be added to the OnLookup trigger for the Value field.

//IF OUR FIELD IS A FIXED VALUE
IF ("Table No." <> 0) AND ("Field No." <> 0) AND ("Value type" = "Value type"::Fixed) THEN BEGIN
  //OPEN THE TABLE AND GET THE FIELD REF
  varRecRef.OPEN("Table No.");
  varFieldRef := varRecRef.FIELD("Field No.");
  //MAKE SURE THE FIELD TYPE IS AN OPTION FIELD
  IF FORMAT(varFieldRef.TYPE) = 'Option' THEN BEGIN
    mystring := varFieldRef.OPTIONSTRING;
    mycapt := varFieldRef.OPTIONCAPTION;
    //CALL OUR FORM INITIALIZE FUNCTION
    lform.SetOptionString(varFieldRef.CAPTION, mycapt);
    IF lform.RUNMODAL = ACTION::LookupOK THEN BEGIN
      lform.GETRECORD(comment);
      //GET THE VALUE FROM THE SELECTED RECORD
      Value := comment.Comment;
    END;
  END;
END;

lform is a Form variable, to a form, built over “Comment Line” (so I don’t need a new table), where the SourceTableTemporary is YES and the LookupMode is YES.

I then added a function to the form called SetOptionString(pFieldCaption : Text[30];pOptionString : Text[250]) with the following code:

1. pFieldCaption – Sets the form Title, via a Global variable OptionFieldName, where the forms DataCaptionExpr = 'Select ' + OptionFieldName
2. pOptionString – the option string of the field we are looking at

OptionFieldName := pFieldCaption;

//MAKE SURE WE FIX THE DOUBLE COMMA “,,”, AS SELECTSTR() CAN’T HANDLE IT, BUT WE DON’T WANT TO MISS IT EITHER
IF STRPOS(pOptionString, ',,') > 0 THEN
  pOptionString := INSSTR(pOptionString, '|', STRPOS(pOptionString, ',,') + 1);
i := 1;
max := 1;
prevChar := ' ';

//LOOP THROUGH THE OPTIONSTRING AND COUNT THE NUMBER OF OPTIONS
WHILE i <= STRLEN(pOptionString) DO
BEGIN
  IF pOptionString[i] = ',' THEN
    max += 1; 
  i += 1;
END;

i := 1;

DELETEALL(FALSE);
//LOOP THROUGH THE OPTIONSTRING, AND FOR EACH OPTION, ADD A RECORD TO THE FORMS TEMP SOURCETABLE
WHILE i <= max DO
BEGIN
  "Line No." := i;
  Comment := SELECTSTR(i, pOptionString);
  //SKIP THE “,,” REPLACEMENT, SO THE USER DOESN’T SELECT IT, BUT THE OPTION INDEX IS STILL CORRECT
  IF Comment <> '|' THEN
    INSERT;
  i += 1;
END;

This returns the selected Text value, which is fine for showing the user what has been selected, but when it comes time to use the value again and we need to place it into the record, the Text value would give an error. Basically because Option fields are Numeric values represented by the OptionString texts.

A new function is thus needed for converting our selected string into its numerical representation. This is needed, regardless of which solution above is used.

ConvertOptionString(pTableNo : Integer; pFieldNo : Integer; pOption : Text[250]) : Integer

1. pTableNo – Which table are we looking at
2. pFieldNo – Which field does it apply to
3. pOption – the selected option string value

//GET THE TABLE AND FIELD REFERENCES
rRef.OPEN(pTableNo);
fRef := rRef.FIELD(pFieldNo); 

//EXTRACT OUR FULL OPTION STRING AND CONVERT TO UPPERCASE TO AVOID CASE MISMATCHES
optionString := UPPERCASE(fRef.OPTIONSTRING); 

//MAKE SURE WE FIX THE DOUBLE COMMA “,,”, AS SELECTSTR() CAN’T HANDLE IT
IF STRPOS(optionString, ',,') > 0 THEN
  optionString:= INSSTR(optionString, '|', STRPOS(optionString, ',,') + 1); 

i := 1;
max := 1;
prevChar := ' '; 

//LOOP THROUGH THE OPTION STRING AND GET THE TOTAL NUMBER OF OPTIONS
WHILE i <= STRLEN(optionString) DO
BEGIN
  IF optionString[i] = ',' THEN
    max += 1;
  i += 1;
END;

i := 1;
//LOOP THROUGH ALL OPTIONS, EXITING IF WE HAVE A MATCH
WHILE i <= max DO
BEGIN
//IF THE VALUE MATCHES THE CURRENT OPTION, THEN EXIT WITH THE OPTION NUMBER, -1 BECAUSE OPTIONS START ON ZERO
  IF SELECTSTR(i, optionString) = UPPERCASE(pOption) THEN
    EXIT(i-1);
  i += 1;
END;

//EXIT WITH ZERO IF WE FIND NOTHING, SAME AS THE DEFAULT BEHAVIOUR OF AND OPTION FIELD
EXIT(0);

And that is as they say that.

There was two things I noticed when working on this.

The first is that if you try to get the OptionString of the Type field on Table 2000000041 (Field), then the Dynamics Nav client in version 5.0 crashes. I haven’t tried it for previous versions or 5 sp1, but something is definitely not right there. It works fine in Nav 2009.

The second was something that I have noticed before, but never had any issue with until now. Table 39 (“Purchase Line”) Field 5 (Type) has the following OptionString value: “ ,G/L Account,Item,,Fixed Asset,Charge (Item)”. Notice, the double commas “,,” in the middle of the string. Standard Nav handles this internally, but if you try to retrieve a value using the built-in SELECTSTR function, it will error out.

NewString := SELECTSTR(Number, CommaString);

This resulted in me having to code a fair bit around the issue. If you know of another way around this, then please let me know. If you want to implement a Dynamic Excel importing function, then once again, let me know and I will happily help you out.