- Microsoft Windows 7 Enterprise
- SITS:Vision Students (v8.7.0)
You might be able to work it out from the online manuals but it took me a while, we wanted to bring back both the code and the lookup value in our XML to our Staging environment.
Why?
Let's take the country of birth of a person as an example as it is expected to be a long list and where just using the parameter &S is not workable. Instead I want to use &G[] and get both the code and the lookup value:
-- What I have: no export format <stu_codc>5826</stu_codc> -- What I could have: export format set to get lookup value -- &GCOD_NAME.COD.SRS <stu_codc>England</stu_codc> -- What I want: export format to get multiple values -- &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>] <stu_codc>5826|England</stu_codc>
- -- What I have: no export format
- <stu_codc>5826</stu_codc>
- -- What I could have: export format set to get lookup value
- -- &GCOD_NAME.COD.SRS
- <stu_codc>England</stu_codc>
- -- What I want: export format to get multiple values
- -- &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]
- <stu_codc>5826|England</stu_codc>
How?
You can see it in the code above but I'll give the run through using the country of domicile with the pipe delimiter as the example: &G[entity.dictionary: <<field_code.entity>>|<<field_name.entity>>:
- Select the exchange fields (or container) to apply this to
- Select the "Export Format" tab and click on the chevrons ("»") next to the field to apply this to
- Enter the dictionary code, the entity code and then the inner SRL text which should be <<field_code.entity>>|<<field_name.entity>>, if you try to apply you should be prompted to convert to use gold characters:
- Test the XET export and you should get something like:
Almost!
If the code and it's short name/full name exist in the database, this works accordingly. If however, for some reason the code does not exist the code lookup will fail:
-- If CODE=0000 and 0000 does not exist in table to lookup name, -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>] -- yields: <stu_codc /> -- But if CODE is not populated, -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>] -- also yields: <stu_codc />
- -- If CODE=0000 and 0000 does not exist in table to lookup name,
- -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]
- -- yields:
- <stu_codc />
- -- But if CODE is not populated,
- -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]
- -- also yields:
- <stu_codc />
-- If CODE=0000 and 0000 does not exist in table to lookup name, -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]&ABLANK="*LookupFailed*" -- yields: <stu_codc>*LookupFailed*</stu_codc> -- But if CODE is not populated, -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]&ABLANK="*LookupFailed*" -- yields: <stu_codc />
- -- If CODE=0000 and 0000 does not exist in table to lookup name,
- -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]&ABLANK="*LookupFailed*"
- -- yields:
- <stu_codc>*LookupFailed*</stu_codc>
- -- But if CODE is not populated,
- -- Using &G[COD.SRS:•<•<COD_CODE.COD•>•>|•<•<COD_NAME.COD•>•>]&ABLANK="*LookupFailed*"
- -- yields:
- <stu_codc />
Our XSLT can then do the following:
<COUNTRY_OF_DOMICILE_CODE> <xsl:value-of select="substring-before(stu_codc,'|')"/> </COUNTRY_OF_DOMICILE_CODE> <COUNTRY_OF_DOMICILE_DECODE> <xsl:value-of select="substring-after(stu_codc,'|')"/> </COUNTRY_OF_DOMICILE_DECODE> -- for *LookupFailed* both code and name will be blank. -- possibly use "-1|*LookupFailed*" for the error to come through the XSLT but watch your datatypes
- <COUNTRY_OF_DOMICILE_CODE>
- <xsl:value-of select="substring-before(stu_codc,'|')"/>
- </COUNTRY_OF_DOMICILE_CODE>
- <COUNTRY_OF_DOMICILE_DECODE>
- <xsl:value-of select="substring-after(stu_codc,'|')"/>
- </COUNTRY_OF_DOMICILE_DECODE>
- -- for *LookupFailed* both code and name will be blank.
- -- possibly use "-1|*LookupFailed*" for the error to come through the XSLT but watch your datatypes
Notes:
We did not find a way to return the code when the lookup failed, only an alternative message "*LookupFailed*".