Our source data contains periods, such as 2230.1500.1000, which are stored as a string. Lookups in Forms don't seem with work with these values. Is there a way around this?
That is expected behavior for nchar (the value is padded with spaces to the length of the field.
I would guess that is the problem here, the values are padded with spaces that are trimmed on the Forms side, so they're no longer matching.
I can still make it work in a table. And I made the description field read-only so no one could change it and it still worked.
Rule Out Troubleshooting -
Have you tried to fill the field with a different column from the table just to rule out any issues with the AcctDesc column specifically?
Have you looked at the SQL table and confirmed that there is indeed information in the AcctDesc column?
Have you attempted to do a lookup and populate field with any other SQL table just to rule out any issues with the GL table?
Have you tried using fields not in a table to see if it will populate the description just to rule out any issues with the Laserfiche table?
Is the lookup filling a number field or single line field? It should fill the single line field with those values.
There is a drop-down list with the strings in question. When the user selects a value from that list I want to do a Lookup to fill a single-line field with related Description field from data source.
Can you post a screenshot of your lookup rules? I can set up a lookup with periods and it works fine.
Screen shot of form with table. Lookup rule should fill Description when account is selected.
Lookup Rules:
Here's a sample from SQL Server:
Both are nchar fields. The GLAcct field is populated in the SQL Server table using the Concat function - CONCAT(BusUnit,'.',ObjAcct,'.',SubAcct)
I also tried this without the table and it still doesn't work. I am also having difficulty in SQL Server just querying the same conditions and getting no results. Perhaps it has something to do with the fields being nchar? I haven't tried this before with the concatenation of periods to separate multiple fields so it's kind of a mystery at this point.
Do all values fail or just the ones that are missing the last group of digits?
All values fail.
What is the length of that column?
I believe Miruna is asking what length the nchar columns that feed the concat statement are. When I recreate the table as nchar, I made them nchar(10) and that set a fixed value of 10 characters so my concat field does not look as expected.
That is expected behavior for nchar (the value is padded with spaces to the length of the field.
I would guess that is the problem here, the values are padded with spaces that are trimmed on the Forms side, so they're no longer matching.
Miruna; you are correct. If I add LTRIM(RTRIM) to the fields before concatenating, the unseen spaces are removed and the lookups now work. Thanks.
Here's the table in SQL Server. It gets created from a SELECT INTO statement and the field data types are generated automatically.
SELECT
CONCAT(GMMCU,'.',GMOBJ,'.',GMSUB) as GLAcct,
GMMCU as BusUnit,
GMOBJ as ObjAcct,
GMSUB as SubAcct,
GMDL01 as AcctDesc
Into GLAccounts
FROM
ROMAINE.ROMAINE.PD920DTA.F0901