fetch date field using Lookup Enricher : Portal

G

Gavin BEANGSTROM

started a topic 6 months ago

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

B

Bharat Joshi said 6 months ago

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

B

Bharat Joshi

said 6 months ago

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

G

Gavin BEANGSTROM

said 6 months ago

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

B

Bharat Joshi

said 6 months ago

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