Home > Forum > Data sources > Problem with using WEBOCN with external SQL in Master Data Management

Problem with using WEBOCN with external SQL in Master Data Management
0

23.02.2021
21:41
Aleš Jurak

We are implementing a solution of Master Data Management and are trying to use Webcon as a User interface with all the logic behind forms. The database is prepared in MS SQL with different tables. The challenge we are having is to update entries with foreing keys.

Scenario:
Table 1: Country has one column (apart from ID) – 'Country'
Table 2: City has two columns 'Country ID' (Choice column from table Country) and 'City'
So in City table, we have one foreign key that shows on Country table.

Issue:
When we update the Country name in SQL – Name of the Country is not automatically updated on the City form. We can update the field with a form rule on page load, but only when we go into Edit mode.

Is there a way that his is also done in read only mode, when we jus to open the Form.

A quite similar scenario applies when printing a document – we want to have the last version of the Partner data.

Best regards

Aleš

23.02.2021
22:28
MVP
Daniel Krüger (Cosmo Consult)

Hi Aleš,

I'm not sure what you are trying to achieve. I'm guessing the following:
- you have one workflow for each table
- each workflow instance represents a row in the table, which is used to update the data
- the foreign key relations are represented by a choose field

What I'm not sure about is, whether all table columns are represented as fields. If this would be the case than I don't see a reason for the external tables. They are just a copy of the data contained within the content DB anyway. Wouldn't it be sufficient to create SQL Views in your database referencing WFElements? This way you could have proper column names instead of WFD_AttText01.

But this is beside your question. :)
Your issue is the default problems with choose fields. The display name is only updated during save. Which is correct in my opinion. If a persons name changes, due to a marriage or so, you must not change it for the past (finished) workflows. We need the correct data at the point in time.
You have two approaches:
1. Add a data row to fetch the value from the other table in view mode and hide it in edit mode. The choose field is displayed only in edit mode.
2. If you change the country value you could update all workflow referencing this value by using the "Update related workflow instance" with a SQL statement identifying all workflows which use this field.

The first approach should help you in form views and documents but will fail in reports. Here you would need to add a calculated field. The second approach would solve this in general.

Best regards,
Daniel

Solution (1) Helpful (0) Wrong answer (0)
25.02.2021
03:12
Aleš Jurak
In reply to: Daniel Krüger (Cosmo Consult)

Hi Aleš,

I'm not sure what you are trying to achieve. I'm guessing the following:
- you have one workflow for each table
- each workflow instance represents a row in the table, which is used to update the data
- the foreign key relations are represented by a choose field

What I'm not sure about is, whether all table columns are represented as fields. If this would be the case than I don't see a reason for the external tables. They are just a copy of the data contained within the content DB anyway. Wouldn't it be sufficient to create SQL Views in your database referencing WFElements? This way you could have proper column names instead of WFD_AttText01.

But this is beside your question. :)
Your issue is the default problems with choose fields. The display name is only updated during save. Which is correct in my opinion. If a persons name changes, due to a marriage or so, you must not change it for the past (finished) workflows. We need the correct data at the point in time.
You have two approaches:
1. Add a data row to fetch the value from the other table in view mode and hide it in edit mode. The choose field is displayed only in edit mode.
2. If you change the country value you could update all workflow referencing this value by using the "Update related workflow instance" with a SQL statement identifying all workflows which use this field.

The first approach should help you in form views and documents but will fail in reports. Here you would need to add a calculated field. The second approach would solve this in general.

Best regards,
Daniel

Hi Daniel,

We were exploring this option as well, but in the end, we decided to go with a separate database. A clear structure for reporting and integration with other applications. The MDM shall be used also as BDC/BCS in Sharepoint. Probably it could be done with views as well.

Thanks for pointing out both options. For the time being, we will use option number one, while data will be always populated directly from MDM database.

Best regards

Aleš

Solution (1) Helpful (0) Wrong answer (0)
Menu
Babinskiego 69
30-393 Krakow, Poland
office@webcon.com
+48 12 443 13 90