Initialize Item List with SQL Query from Oracle DB

0

Hello Andreia,

from what I can tell this should work.
I also did a small test:
1. Data Source definition, using a new connection to the Config DB
2. Setup of the item list initialization
3. Testing the expression editor result
4. Starting the process

Which step doesn't work in your case?

Off topic:
I really like the new diagnostic mode, where I can see that my query has been executed :)

Kind regards,
Daniel

Hi Daniel,

It worked, I was using the alias from the table to initialize the item list but when I switched to the names of the columns it worked.

But I have question, did you ever initialize the item list using a where condition set on a field from the form?
In my case there's this project id which also exists in my table and I want to initialize it with the value that is in the form but it's not working.

SELECT DESCRIPTION as {DCNCOL:185},
due_date as {DCNCOL:186},
ROUND(bill_trans_bill_amount,4) as {DCNCOL:187},
bill_trans_currency_code as {DCNCOL:188},
ROUND(bill_trans_bill_amount_eur,4) as {DCNCOL:189}
FROM bi_obiee_dw.fact_pl_invoicing
WHERE invoice_number IS NOT NULL
and '{WFCON:2289}'=project_id

When I try to test it it returns an empty window.
I changed the field to be integer (previous was text) but still not working.

Hope you got any ideas on this!

Andreia

Hi,
Yes, it should work if your statement is correct.
You can preview expression by using 'Show' button in editor to check what value is behind form field placeholder.

Hi Andreia,


I'm adding to Sebastians answer. The preview should contain the value from the field for example 'P-1000' and not just '' without a value.

If you want to initialize the item list when the value of a field changes you could add a form rule to "On value change" (1) in the "Style and behavior" tab. You can use the "Item list -> Initialization" function.

I haven't test it but you should probably see the executed SQL query if you enable diagnostics like in this example:
https://daniels-notes.de/posts/2021/series-expert-guide-part-3#with-webcon-bps-2021

My personal preference though would be to create an additional step. For example "Initialization - Part 1" and "Initialization - Part 2" and check the Wizard mode for the path. During path transition I would initialize the item list with an action.

Best regards,
Daniel