For the longest time, I have had this uncomfortable feeling in the back of my neck that I am struggling too hard. Things should be easier.
I have had several challenges that are somewhat related but I just could not figure out how.
These are the challenges 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 and moving it into the system without a lot of manual steps. Typical use cases are 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 an Excel plugin and did a ViewModel pattern that worked really nicely to allow for a fast definition of new exports and imports. 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 if this was easy to implement, I just know that other developers will not like using something like that – simply too 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 pop up without warning and must be done immediately. 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 are 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. However, the search mechanism to find objects is limited – and the UI is due for an overhaul. I 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. If we can allow for the definition of sending and receiving ViewModels 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 pair of ViewModels. 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 one 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 must still not lose track of business rules, derivations, 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 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 in this form.
In the sample above, I have a standard seeker – it searches on Thing.Guid and 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 read-only 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 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, VMClass, code-generated ViewModels, WPF, 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. Give it some thought. If your sending system knows what data to send – and how to make it tabular – possibly by dividing the data into multiple ViewModels. 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 the search, and save.
Updates 2018-06-13
One issue with the import mechanism was that even if it works well for keys that are of type string, it did not work very well if the key was of type Guid. The reason is that we get the key from the first column as a string, but we expect the vSeekParam to then get the contents of one key prior to the OCL-PS-query being executed. The OCL-PS query gets translated to SQL and the SQL may throw an exception if we try and compare a string parameter (vSeekParam) with a Guid.
To mitigate this, we now check for additional variables that you can define:
vSeekParam : String -- This is the standard seeker param
vSeekParamAsGuid : Guid -- this will be filled prior to search if it exists and if the key parses as a Guid
vSeekParamAsInt : Integer -- this will be filled prior to search if it exists and if the key parses as an int