Data Synchronising from External MSSQL db to Outsystems Internal DB Entity

Data Synchronising from External MSSQL db to Outsystems Internal DB Entity

  
Hi All,
I'm trying to create an application that will use MSSQL data that is created by another application. This other application is the master SQL data creator and the application cannot be changed as it is an 'off the shelf' piece of software.
My new application will view this master SQL data and I want to create screens that shows this data along with new Entity data.
I've created an extension and I can see all all the master SQL data in my new app, if I understand correctly - even though I can see the data, Outsystems is limited in it's functionality with regard to manipulating and adding to this data (new Entites / fields)
So.... I need to copy the master data to a new entitiy so that I can work with it, my problem is - how do I keep the entity up to date with the master data?
I have followed the Outsystems demo of looping through an excel file to populate an Entity and have tried to amend this to use a DBQuery rather than an excel import - see screen grab.
It works, except it only brings one record in..... can anyone help me fix this so it can bring ALL the records in.
Please help.... I've spent 3 days solid on this now and cannot proceed with my project until this is resolved..... job stopped..!
Cheers
Gary
Added amended screen grab to make it easier to follow...
Hi Gary,

I don't quite understand what you need to do. Do you only need to manipulate data (insert, update, delete) or do you have to mantipulate metadata (change the structure of the table) too? The first thing is already just possible with AgilePlatform.

Regards, Harry
Hi Harry,
Thank you for your response.
I want to display the source data - no changes, but I want to be able to display it in a grid along side new data i.e.
One of the source data tables displays information about a 'Property' - 'Address', 'Postcode', 'Owner' etc.... in my new application I want to create a new table Called 'Repairs' and have fields like 'Repair Description', 'Cost', 'Due Date', 'Priority' etc.... and I want to associate each row of the 'Repair' table with the 'address' of the 'Property' table
My problem is that I can't create a grid that refernces and internal db and an external one - I get the error detailed here http://www.outsystems.com/NetworkForums/ViewTopic.aspx?Topic=How-do-I-import-data-from-external-DB-into-Outsystems-entity%3f#Post1
The answer in this post suggests copying the data from the external extension and create a timer to refresh the data..... I just don't know how to do it though..!
Thanks in advance....
Gary
Hi Gary,

Make sure you have a condition to match the record by id correctly set in the second query 'NewDataTable' or else instead of creating several records that action will always be updating the same record. If you can upload your oml it might be easier to understand your problem and to help you.

Cheers,
Tiago Simões

Hi Tiago,
OML attached.... I guess I'm close, but I can't workout what condition I need to set to process all records rather than one.
Screengrab to show where my action is.
It's trying to replicate all data held in extension entitiy 'GemProperty' into local Entitiy 'Property'
Thanks for your help..!
Gary
Hi Gary,

Yes, you were really close. You just need to send the correct argument:



Cheers,
Tiago Simões
Hi Tiago,
Maybe I have another problem.... as the suggested Id gives me an error as below...

Hope you can spot my error..!
Cheers
Gary
My bad,

It should be "GemPropQuery.List.Current.GemProperty.Reference".
Also don't forget to complete the assign with all the other values.

Cheers,
Tiago
Hi Tiago,
Now I get another error... did it work OK on your PC? 


Thanks Gary



Hi Gary,

That is just a warning, but in reallity you want the identifiers to be kept, right?
Don't forget to auto assign all the values from the imported record to the new table:



And then setting the identifier:


It should give another warning but again you don't need to worry about it.

Another alternative, if you don't need to keep the same identifier, would be to create an autonumber id attribute in the new entity and just add a different attrbute that could be a foreing key to the record on the old table.

Cheers,
Tiago Simões

Hi Gary,

Also to get rid of the warning, you can either Hide it (right click on the warning and Hide) or in the assign use IntegerToEntityRefInteger(EntityRefIntegerToInteger( ... )) in that assign expression.

None of that will change the behavior, it's just a consistency warning to avoid mistakes.

Regards,
João Rosado
Tiago,
Thank you so much for all your help, I know have a working data sync.... you stopped me from going insane..!!!!
I can't thank you enough :-)
João, I like the sound of your second option as it removes the error, but unfortunatley I don't understand what to do.... so I opted for hiding the message.
Cheers
Gary