For the longest time I have had this creepy feeling in the back of my neck that I am struggling to hard. Things should be easier…
I have had several challenges that sort of are related but I just could not figure out how.
These are the challenge’s I am referring to:
1. Excel – import, export and excel governed search
End users of the PIM system we built for Perrigo Nordics really wanted to do certain things with Excel:
- Extract any grid content to excel
- Mark any area in excel – and use that as a multi-search in the System (typically a row of SAP-Material-Numbers, or barcodes or anything really)
- Having tabular data in excel – move it into the system without a lot of manual steps. Typical use cases is that we get an excel-based price list from a partner – and we need it in the system in order to use it in our calculations and business rules. Or we get an excel from a partner with WAY more detailed information than what anyone has dared to think of in SAP (in store campaign per SKU POS values that we need to keep in order to verify agreements with precision)
For these particular use-cases we created a Excel-plugin and we did a ViewModel pattern that worked really nice to allow for fast definition of new exports and imports. But I did not like that the Excel-plugin had to know what the Client-application was called in order to find the named pipe to send data to and from the system. Even it this was very easy to implement I just know that other developers will not like using something like that – simply to many moving parts to explain.
2. Batch data load and update
I am certain that you too have the need to batch insert or update data. These needs often popup without warning and must be done today. The needs may be short term, even one time only – or they may be needed daily, weekly or on some signal. Ideally we need to remember these needs – since they constitute some kind of use case that has posed requirements on our system.
Today this is handled by ViewModel definitions and the debugger (manual) – or MDriven Server serverside viewmodels (automated).
It uses a common strategy to work with tab-separated data – where the first column is the key and the following columns is data to set.
If the key is not found – an object is created.
To set single links from imported data one can use Comboboxes in the row definitions – and string matching will be performed to find the correct object. It is a simple and powerful approach. But the search mechanism to find objects is limited – and the UI is due for an overhaul – and I just feel that this is basically the same as the excel import described above.
3. Synchronization between different MDriven systems
We are working with Information Experience and they do the coolest things with Hololens – Unity – Large IFC models and Turnkey. They have a particular need for offline access to data. This offline system will be an MDriven based system with a local db. But it will interact with the central Turnkey system when possible. This requires data synchronization between two systems that will have different – but similar models. I do not believe in fully automated synchronization schemes – my experience is that you will always reach much further by actually understanding what data to move around and in what direction. But if we can allow for definition of sending and receiving view models in the two models – and that the senders and receivers are made compatible by name matching – then we would only need to create a generic logic to start the synchronization between any such a pair of view models – and this logic is of course ridiculously similar to the use-cases described above.
4. All the above in native MDriven and in Turnkey-MDriven
The need to treat tabular data as a liquid – to be able to pour it from once source to another – even if it is a cloud based Turnkey system with HTML UI – a WPF based system – an Excel spreadsheet – an SQL-Server – a REST service. When pouring data like this we still must not lose track of business rules, of derivations, of access groups and user authorization. We must also have the connections documented so that we know what is used by whom. The pouring of data must also manage merge and update as well as inserts – it must be able to set all types of attributes and associations on objects. It also needs to be transaction based.
That is the challenge.
The solution
I cannot say that every micro detail is ironed out yet – but I do think that the solution we are checking in today is a real step forward. The goal is to have one solution and one code base that will be able to handle all of the needs above.
In order to import data you must declare a seeker viewmodel.
The columns you want to write to must be editable in the search result grid.
If you make the default search field multi-line it will accept tab-signs and multiple lines – clipboard data normally comes from excel that way.
In the sample above I have a standard seeker – it searches on Thing.Guid – and it shows the Guid in the first column.
The following search result grid columns are made editable.
Also note that the SeekValue (vSeekParam) is made multi-line so that it will accept newlines in pasted data.
Running this in the debugger I can search for a pack of Guids:
If I paste more tab separated values – then the logic will try and write data:
793e00a1-7e61-4261-a2ed-7e9047e2c5c4 My new value 1
6d47193c-e4b9-486b-827d-104e01466a81 My new value 2
f084a714-2ae6-43a5-8862-74c912861aae My new value 3
This will work for all columns – columns made readonly will be skipped – columns that are comboboxes will string match set the correct value.
The first column is always the key. If the key is not found in a search (using the criteria(s) of your seeker) the logic will look for a ViewModel action named CreateNew. If the this action is found – it should create a new object. This new object will be added to the search result.
I will change the Guids a bit to show my point:
77777777-7e61-4261-a2ed-7e9047e2c5c4 My new value 4
88888888-e4b9-486b-827d-104e01466a81 My new value 5
99999999-2ae6-43a5-8862-74c912861aae My new value 6
In this case – new objects were created and dressed with data. I can verify this by searching on the complete list:
793e00a1-7e61-4261-a2ed-7e9047e2c5c4
6d47193c-e4b9-486b-827d-104e01466a81
f084a714-2ae6-43a5-8862-74c912861aae
77777777-7e61-4261-a2ed-7e9047e2c5c4
88888888-e4b9-486b-827d-104e01466a81
99999999-2ae6-43a5-8862-74c912861aae
Since this logic is now part of the ViewModel SeekLogic it will work everywhere. It works in Turnkey, In VMClass, In codegenerated ViewModels, In WPF, In the debugger and in server side Viewmodels in MDriven Server.
It may not be clear to you how this will help you to synchronize data from one model to another. But give it some thought. If your sending system knows what data to send – and how to make it tabular – possibly by dividing the data in multiple viewmodels. And if your receiving system has defined one or many import seekers like this. Then your sending system only needs to set the vSeekParam of the receiver with tabular data – execute search – then save.