A question that comes up from time to time is – “What order does SmartConnect process the source data in?”
In any version of SmartConnect – SmartConnect.com, SmartConnect 21, or SmartConnect 2018, the Key Fields you select on the map data source determine two things:
1: What grouping of data that together would form one “document” (which could be 1 source record or multiple depending on the destination)
2: The order of the data processed
Let’s take a SQL Query run in SSMS
In this query, a few fields from the RM00101 are displayed along with a random guid using the SQL newid() function and sorted by that random guid value- this shows that the data is coming back randomly from our source to have “un-ordered” data for this example.
For our purposes we want 1 record for each document and since we know in this data, the CUSTNMBR is the primary key on the table, and we could choose that value as a key field for the map. But from #2 above, that would order the output data in CUSTNMBR order which isn’t what we want to see.
What we would like to do is process the output data in STATE, then CITY, then CUSTNMBR order.
Here, I chose my Data Source Key Fields in that same order – STATE, then CITY, then CUSTNMBR and pressing OK, SmartConnect shows them in alphabetical order. This is just a UI/Display and doesn’t affect how the SmartConnect map will process the data.
Then I map the text file output to the columns to easily show this behavior, it would be the same for any destination type.
Running the map – it runs successfully and outputs the data to the csv file but opening it to review the output we notice that the data is NOT in STATE, CITY, CUSTNMBR order as we had expected.
Clearly the data is sorted by CITY order – we can’t really tell what past that it is being sorted by – but it surely isn’t by STATE first!
Why is SmartConnect sorting the data in this order?
Currently all versions of SmartConnect do not capture the selected order of the fields – nor does it use the display/alphabetical order of the fields selected.
Currently it is a bit of luck on how the data is ordered due to how the KeyField data is populated in SmartConnect. Normally it doesn’t matter the processed order and so this doesn’t come up – but sometimes it does matter, like my expectation for this example that the data is in a specific order.
When the Key fields values are written into the KeyColumn table, the PK on the table is a guid and that is generated at random exactly the same way that the newid() value was used in my initial SQL statement for the data source.
If we have one key field – well that’s easy. If we have two key fields, then I’d assume 50/50 chance of it coming out in the order you were looking for, and it does down from there.
If we write a SQL Query to look at the order that SmartConnect is going to evaluate the data, this query will show that – just change the map name to your map name.
declare @identifier as uniqueidentifier
select @identifier = MapId from map where id =’GP_CUSTOMER_TO_TEXT’
select * from KeyColumn where mapid = @identifier
The results for my map are shown below and seem to confirm that “CITY” was the first field being sorted in the data output. Then STATE and then CUSTNMBR.
We could just change our key field – switch to DEX_ROW_ID or the Column1 field and re-save the map, and then re-select in the order that we want. With 2 fields – it is 50/50 and so it should come out as we want. But if you have 3 or 4 fields, that is less likely to come out as we’re looking for and it is easier to just adjust it directly in SQL.
First, make a SQL Backup of the SmartConnect database. Or if that isn’t easily possible, at least make an export of the map just in case!
Next, we can write update statements to “fix” the KeyColumnId values to make sure they are in the order that we want them to be. In my example, I adjusted the KeyColumnId for CITY & CUSTNMBR. Then I re-run the initial select to verify that the fields show in the order I want.
–fix the city to be after state
update KeyColumn set KeyColumnId = ‘709FB7D9-BE19-4B7D-A11B-B0CB0030ECCF’ where KeyColumnId = ‘709FB7D9-BE19-4B7D-A11B-B0CB0030ECCE’
–and then make sure CUSTNMBR after CITY by incrementing GUID by 1 CCF->CD1
update KeyColumn set KeyColumnId = ‘709FB7D9-BE19-4B7D-A11B-B0CB0030ECD1′ where KeyColumnId = ’68B89747-7EEB-4C61-BBC1-B0CB0030ECCE’
declare @identifier as uniqueidentifier
select @identifier = MapId from map where id =’GP_CUSTOMER_TO_TEXT’
select * from KeyColumn where mapid = @identifier
Your GUID values will be unique, therefore you’ll have to determine the proper values for your data to set them to!
Running the map and reviewing the output data shows 3 sections with like State/City and we can see the data is being sorted to the proper STATE, CITY, and CUSTNMBR order with this change.
