In my SmartConnect integration, I need it to process the data in the order specified by my Data Source Keys (SC 21 name – Key Fields in SmartConnect 2018).
On one of my maps, it does order the data as I expect but in the other it does not even though the same fields have been selected.
When I preview the SQL Query from the Data Source (or Preview from the map, it data sorts in the order that I want and expect).
The Key Fields/Data Source Keys on the map are the ones I selected however I did select them in the order I wanted to see the data – Zip, ADDRESS2, DEX_ROW_ID – but they do not show in the correct order on the UI.
If I preview inside of my destination mapping (or run the map), then SmartConnect is changing the sort of my data to something I don’t want it to be.
It appears that the data is sorted by DEX_ROW_ID or possibly by ADDRESS2 and then DEX_ROW_ID.
How can I make SmartConnect sort the data as I want it to?
In the SmartConnect UI, the Data Source Keys are displayed in alphabetical order – so that isn’t a representation of how the data will be processed/sorted.
Also the order that you select the key fields in doesn’t matter either as that information is not stored in SmartConnect, just the fields.
The processing of the source data to determine the number of “documents” for a map has the side effect of determining the order the data is processed.
Unfortunately, SmartConnect currently does not have the ability to specify this exactly and the order of the key fields and therefore the sort is essentially random.
When SmartConnect saves the map, it writes the Data Source Keys to the KeyColumn table. Running a query to display the Key Field information for this map. SmartConnect does not order by name, it just queries the table directly and the PK on the table is the Id which is just a randomly generated GUID which is why the data sorts “randomly” sometimes if multiple keys are specified. Since a lot of maps have just one Key Field defined or else don’t care exactly the order of the data processed this issue doesn’t come up too often.
We see how SmartConnect is then sorting/processing the data read by the source query.
From the screenshot, we can see that the order the data would process as was really ADDRESS2, DEX_ROW_ID, Zip. Since the data for ADDRESS2 was blank for most records, that is why it appeared it could have been by DEX_ROW_ID then ADDRESS2.
In order to get SmartConnect to process the data in the order we want – Zip, ADDRESS2, DEX_ROW_ID – we then have to “fix” the data by hand to make sure the query above pulls the fields in that same order.
While I don’t typically recommend ever trying edit the SmartConnect metadata on the back-end, in this case nothing in SmartConnect relies/uses that KeyColumn.KeyColumnId field and is just a unique field on the table so that each row has a unique index on it. Therefore, this is relatively safe to change on the back-end however make sure you have a SQL Back up just in case before you do anything.
To fix this, we just need to update the KeyColumnId value for the Zip field to be BEFORE the ADDRESS2 value. It can be anything, just has to be smaller than – easiest is just use the KeyColumnId for ADDRESS2 and set to 498 vs 499.
Make sure that SmartConnect does not have the map open – otherwise you will get errors trying to save the map due to changing the back-end data when SmartConnect isn’t expect that to happen!
Running the update statement and then re-querying the KeyColumn table now shows the fields in the order that we want SmartConnect to use and process the data in that order.
Running the map to the destination or using Preview within the mapping shows that SmartConnect IS using our new Key Field defined sort order as it is sorting by Zip, ADDRESS2, DEX_ROW_ID now.
Things we learned in this article:
1: SmartConnect previews data at the map/data source level by however the source returned them. In the example here, it was defined in the SQL Query by the “order by” clause.
2: SmartConnect previews data at the at the mapping level or processing the map run by the Key Fields selected – but in “random” order. How random essentially depends on how many key fields were selected since the order by is determined by a randomly created GUID value.
3: Data Source Key fields are displayed in the UI by alphabetical order, not the order selected. Neither plays any part in the actual order the data is sorted & processed by.
4: The processing order by is determined by the data in the KeyColumn table. In order to force the order we want, we might have to resort to hand editing the data per the article to make the KeyColumnId values in the order we want the key fields used.
