Invoke REST API / Field forms populate

0

Hi Agnieszka,

I haven't used a similar example but I would check the following:
- property value
It's of type collection and I would assume, that you won't be able to assign a value from a collection to a single field. You would need to select an item list as the destination field for the collection.
- collection/array with objects
I haven't had this case where the collection/array contains objects. I don't know whether this will work.

If you can't make it work with an item list, you try to store the Response:Body in a multi line field and use SQL to extract the information.
https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16&redirectedfrom=MSDN

Best regards,
Daniel

Hi Agnieszka, Hi Daniel

I'm assuming that Daniel is right - we can't map collection items into single fields - Webcon expects item list.

There shouldn't be a need to store the body inside of a multi-line field though - it should be accessible through business rule on mapping page as 'Input value' parameter.
I'm parsing XML from REST-like API which returns XML instead of JSON. In attachment example mapping, and business rule used to get the XML content.

With JSON though it might be more tricky as OPENJSON requires database Compatibility Level at 130, and by default Webcon database are at 120 according to this*
You should be able to check it with this SQL:
SELECT compatibility_level
FROM sys.databases WHERE name = 'BPS_Content';

However i'm thinking right now, that you might be able to create an empty database with high compatibility level, create a new connection, and use that connection to query with OPENJSON available.

*https://community.webcon.com/posts/post/compatibility-level-of-webcon-bps-databases-good-practices/323/3

Daniel,

Thank you very much for your help. It worked with item list. My first assumption was that if I know that my query returns only one row, it should fit into text fields - obviously I was wrong, BPS doesn't know how many records will the result have and needs to map the table rather than single row.
I attached my mapping of item list to collection - perhaps it will be helpful for others.