Offer tracking

0

Hi Matthias,

it's not that simple to understand your requirement.

I understand that you have a MSSQL Data Source in WEBCON that already gets the offers from your ERP and you wish a view in the portal to display/analyze this data.

Unfortunately data sources can't be used as sources in reports yet as you've mentioned already.

The workaround I use to just display data from data sources is as described.

Create a process "Offer Tracking"
Add a form field of type "data table" and select the MSSQL ERP data source.
Configure the advanced configuration to select the columns to be displayed and other options.

Unfortunately data table form fields have no filter or search options in the portal like they exist for reports.

If you need filtering you need to add further form fields.
E.g. a form field of type choice picker as dropdown.
As its data source you choose the Current MSSQL Connection and insert e.g. this query

SELECT *
FROM (
VALUES
(10, 'won'),
(20, 'lost'),
(30, 'in progress')
) AS t (stateId, State);

If you want the states to be read directly from the data source you need a business rule "BR-ERP-states" with data source value to get the state values as returned column and place the data source value in a DISTINCT.
Then use this query to populate the state filter dropdown instead of the above query:

SELECT item
FROM dbo.splitToTable('##BR-ERP-states##',';')

Select the business rules through the functions tab to include it to the query.

If you want to use the business rule value in a data source filter you need to write this business rule to a multiline text form field set as technical field. This is needed because no business rules are supported in data source filters. You could now also put this technical field in the above query instead of the business rule.

Now you can use the state filter field in the data source filter of the data table field to match the data source column with the state to the state form field.

Use the similar procedure for other filtering needs.

To deactivate the unsaved data alert when leaving the form you can follow this blog post:
https://sebastien-anselment.github.io/blog/posts/2025/06/30/disable-unsaved-data-alert-when-leaving-a-form/

If you want the comfort of reports you can also create a dictionary process with a form field for each relevant column of the ERP data source and use a cyclical timer in global actions to import/update the ERP offer data to the dictionary. This way the data is replicated e.g. once per day. This reduces traffic with ERP but leads to a loss of realtime data.

I hope this helps you a bit.

Kind regards,
Sébastien