CreateORUpdate on External DB with Foreign / primary key constraints

CreateORUpdate on External DB with Foreign / primary key constraints

  

Hi there,


I'm using an external MS SQL Database and have created some screens without a problem. However, I now have a scenario where I need to create / update a table which joins other tables together.


The database schema is designed in such a way in that there is a dedicated table for Collections and a dedicated table for the DataDictionary (which stores definitions). These are joined through two link tables, CollectionVersion and CollectionVersionDataDictionary as demonstrated in the SQL query below.


  Select *

  from collection c

  join CollectionVersion cv on c.ID = cv.CollectionID

  join CollectionVersionDataDictionary cvdd on cv.ID = cvdd.CollectionVersionID

  join DataDictionary dd on cvdd.DataDictionaryID = dd.ID


In addition, the tables do not auto ID the primary key.


I want my Create / Update command to just update the CollectionVersionDataDictionary table (as I update the CollectionVersion table separately) and if possible, I want to be able to specify the values being inserted. At the minute, I do not know how to see which values are being inserted and this is limiting my ability to debug.


If you need more info or need me to 'share' my application, please advise how as I'm a novice.

If 'support' can get into my application then, in the UI, click on 'Collections' in the Menu and then click on the 'Definitions' button on one of the rows in the Collections screen. This will present all Definitions (from the data dictionary) associated to that Collection. Using an Editable table, I've created a 'Save' action using the CreateOrUpdate method. However, I'm getting key constraints when the system attempts to update the table - either editing a row or adding a new row. I believe this is down to the complexity of the way the tables are actually joined together and I've not selected the correct data to insert back.


Thank you!!!

Hi Chris,

If I understand you correctly, and please correct me if I am wrong, this is a simplified version of the data model that you are working with:


Collection
----------

ID (no auto ID)
... some other fields...

DataDictionary
--------------

ID (no auto ID)
... some other fields...

CollectionVersion
-----------------

ID (no auto ID)
CollectionID --------------------------- foreign key to Collection.ID
... some other fields...

CollectionVersionDataDictionary
-------------------------------

ID (no auto ID)
CollectionVersionID -------------------- foreign key to CollectionVersion.ID
DataDictionaryID ----------------------- foreign key to DataDictionary.ID
... some other fields...


With that model in mind, if you want to create a new entry in CollectionVersion, or update an existing one, you need to ensure that the value in CollectionVersion.CollectionID is set to a value that exists in Collection.ID. This means that the argument that you pass to CreateOrUpdate action must have that attribute filled. The return of this function will include the ID of the object that was just created/updated.

Similarly, if you want to create a new entry in CollectionVersionDataDictionary, or update an existing one, you need to ensure that the value in CollectionVersionDataDictionary.CollectionVersionID is set to a value that exists in CollectionVersion.ID and the value in CollectionVersionDataDictionary.DataDictionaryID is set to a value that exists in DataDictionary.ID. This means that the argument that you pass to CreateOrUpdate action must have those two attributes filled. The return of this function will include the ID of the object that was just created/updated.


So, from your question, it looks like what you need to do is:

  • Do a CreateOrUpdate for CollectionVersion table (you mention that you are already doing this), store the return value as CollectionID
  • Do a CreateOrUpdate for DataDictionary table, store the return value as DataDictionaryID
  • Use the CollectionID, DataDictionaryID values in the input record for the CreateOrUpdate for CollectionVersionDataDictionary


I know that my explanation sounds confusing, but without further details about your specific application, this is as far as I can go while only guessing. I hope it is still good enough to help you.

If this does not solve your problem and you would like to send us a version of your application, you can download it from https://<yourserver>/ServiceCenter, then go into Factory, then clicking on your application, then choosing to download it, then attaching it here in the forum.

Regards,

Ricardo Marques

Hey Ricardo,

All makes sense but I'm struggling with two things:

1. How do I stored outputs / return values? As you have described above, the CreateOrUpdate outputs a value, how do I access this?

2. How do I assign these values as inputs to the CreateOrUpdate? Every time I add local input parameters they appear in other screens too.

The main issue that if I create a screen from entity CollectionVersionDataDictionary an then a 'details' screen, the screen only displayed the ID of the table as a dropdown. It doesn't show the other fields, as these are foreign keys to other tables. In the expression editor these fields appear with little arrows next to them. Without these fields presenting on screen, it means they need to be completed by the code and note the UI - which is the ideal after all.

Your understanding of the schema is correct by the way.

Please advise.


Many Thanks


Hi Chris,

Could you send me the application and I'll try to explain how you can achieve this? It is probably much easier if I don't have to guess...


Regards,
Ricardo

Hi Ricardo,

Is there a way I can share the application with you without making it publicly available through the forum? If I do share it through the forum, will other users be able to access my external database (MS SQL) which I've made available through an extension?

The questions I have - although specific to this problem - are generic in terms of understanding how I create input variables on screens using outputs from other screens / methods.

Thanks for your help so far!


Chris

Hi Chris,

You can put the file on a "semi-public" location (google drive, for example), with restricted sharing options, and send me the link via private message.

Alternatively,  If that does not work for you, you can open your file in the Development Environment, then go to Help menu and click Submit feedback (attaching the oml file), include some relevant information and I'll then look for it in our backoffice.

Regards,

Ricardo


Thanks Ricardo - I've done this now via OneDrive. Thanks

Solution

Hi Chris,

I've uploaded a version that should solve your issue. You might not be able to publish it directly as I made some changes so that I could have the tables that in your case are external, but you can surely open it and see my changes, just look for comments with my name... Please do not use my solution directly, as it is a rough solution just for the sake of demoing how to achieve what you want to achieve - you would need to polish it first before using in a real system.

In short, what I did was ensure that you always check the table for the last record there before adding a new one, in order to set the ID correctly (because you don't have auto ID on those fields), and ensure that the proper parameters are passed as inputs, where and when you need them.

The use case that you wanted to accomplish, i.e., add a record in one screen, then use the ID of that record in another screen, can only be accomplished in two steps: first you create the record and save it, then when you have the record on the list, you can pass it's ID to the other screen.

Hope this helps you proceed.


Regards,
Ricardo

Solution

Thanks Ricardo!