Start a new topic
Discussions xDM xDM - Model and Application Design
Answered

fetch date field using Lookup Enricher

I am trying to lookup a date value from another table and default it to a date value on my current form using the lookup enricher.


The problem is that the enricher specifically says the input and output parameters must be string.


It works by converting the date to string if its destination is a string field, but not if its a date field.


Is there a way around this or should I rather create a Database Function

Best Answer

Hello Gavin,


Yes it is true that ,The lookup value expected and output values emitted by this plug-in are string values 

However i can suggest you a way to achieve your use case with the help of function and enricher

You can create a database function to convert the date into string add this database function in your App builder

CREATE OR REPLACE FUNCTION DateToString(dateColumn DATE, Pattern VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    newString VARCHAR;
BEGIN
    -- Check if the dateColumn is null
    IF dateColumn IS NULL THEN
        newString := '';
    ELSE
        -- Format the date according to the Pattern
      newString := TO_CHAR(dateColumn, Pattern);
    END IF;

    RETURN newString;
END;
$$ LANGUAGE plpgsql;


Now use this function in an enricher to convert the Date to string  as below

Here instring is an string type and it will be enriched with the converted date


Now you can use this instring attribute in your output column of the lookup


Thanks

3 CommentsSorted by Oldest First
Answer

Hello Gavin,


Yes it is true that ,The lookup value expected and output values emitted by this plug-in are string values 

However i can suggest you a way to achieve your use case with the help of function and enricher

You can create a database function to convert the date into string add this database function in your App builder

CREATE OR REPLACE FUNCTION DateToString(dateColumn DATE, Pattern VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    newString VARCHAR;
BEGIN
    -- Check if the dateColumn is null
    IF dateColumn IS NULL THEN
        newString := '';
    ELSE
        -- Format the date according to the Pattern
      newString := TO_CHAR(dateColumn, Pattern);
    END IF;

    RETURN newString;
END;
$$ LANGUAGE plpgsql;


Now use this function in an enricher to convert the Date to string  as below

Here instring is an string type and it will be enriched with the converted date


Now you can use this instring attribute in your output column of the lookup


Thanks

Thanks for your response, but I need to get a date value back, if I map it to a string field it works fine. as I can specify the date to string conversion on the output column definition of the lookup enricher.


Using a db function will work though to return the date im looking for

Yes With the DB function i shown it will return the date which you will look for 

Login to post a comment
More topics in xDM - Model and Application Design
See all 237 topics
Help Desk Software by Freshdesk