Look-ups! no-code Transform Stage
Last updated
Last updated
Look-ups are a very effective way of pulling data into your document from other collections and documents. We will look at using the no-code lookup transformation stage and after take a look at a lookup on multiple fields using a low-code JSON stage.
To start we are going to create two Data-In collections. One for Orders and another for Price Mapping. Lets look at loading in the order documents - Download them here.
Lets Create a collection and we will load this document as the Sample Data. Lets have a look at how i set this up
We have the title, cafe Orders In, setup the SAMPLE DATA as per the doc about and I have selected the OrderNumber field as the Identifier. Not only will this help the system identify the document by this number, but it will also now display on the Transaction page so that users can identify the documents.
We can now see these orders, with their order numbers in the transaction page.
Now lets load the Price Mapping File into a separate collection
Lets take a look here, so I have added the sample file and just hit the JSON tab to view the json view of the document. So I have added in a Batch field here of "Item" to break up the file into multiple packets of data which we can lookup. I have then added both the CustomerCode and ProductCode as identifiers, this will help identify each packed by the CustomerCode and the ProductCode. You will see I have made Ignore Duplicates active for this one, as this is "static" data (it is not going to change too often) we can use this to ignore any document sent in which doesn't have any changes. This can just help reduce resource overhead in utopia.
Here we can see all he mapping entries are loaded and have been split up correctly.
Now that all the data is in, lets get stuck into a lookup!
Newt step is to create a Transform off our Orders collection, for me that is 'Cafe Orders In'
I have given it a new Title Cafe Orders TF (TF for transformation) and we can see our 3 orders loaded into the left hand panel. you can cycle through them and find the order where invoiceName is Central Perk.
Lets add a Transform stage and get that setup.
Lets see how I have set this up, I have called it PriceLookup and selected PriceMapping as the collection I would like to merge data from and into my current document. I have Selected the "From Field" to be the "ProductCode" and we want Utopia to try match this with the "Local Field" "OrderItems.ItemNumber". We have also asked it to select the First Match. Lets see what it looks like in the designer "AFTER" view, so lets save and take a look.
Great! it has found some matches and pulled in the data packet which is associated with it. Here we are looking at LineNumber 2 and we can see if has found the Product Code and ItemNumber match and given us a Price 5, which we can now use in the document.
Now lets take a look at how this document looks in the order orders, but selecting them in the panel on the left.
Oh dear! The order from Luke's Diner has found a match on the first line but not for line 2. In fact, the Price field doesn't even exist here. This could be problematic for any calculations further down the line. So what do we want to do in this scenario, lets default this price to 0 bucks and give it a ProductCode of "No Data Found" to tell us that the lookup mapping was unsuccessful.
So lets edit the PriceLookup stage again and edit the setup.
Here I have now selected the "Create default values for non matching" tick box and it has brought up the scheme from the PriceMapping document.
I have also now made the Default scheme to include a Price = 0 and ProductCode = 'No Data Found'. whether you add values here or just leave the default scheme as null, this function is very useful to ensure stability in your calculations later on in your transform. Lets save and see how it looks.
Great the new Scheme is here for the not found section, but there is an _header field here? All documents in Utopia contain this information and it can be useful. perhaps you are using the createdOn date in a calculation, however in this scenario we do not use it and it seems to just be polluting my view with unnecessary fields. So I am going to remove it. Lets edit the PriceLookup stage again and see how we do this.
If you click on the left of "_header" you will bring up the menu and you can remove the field using these tools. You can also manually add and adjust the documents from here. (you will see these functions in other places like projects etc).
Now this looks better! lets save and take another look.
Great, this is looking better! You now have completed a look up stage, This might be as far as you need to go! But lets add some complexity here and dive deeper into lookups, so lets save this transform and now manually post these two spreadsheets into the PriceMapping Collection
OK! these have loaded now... lets jump back into the PriceLookup stage in the transform to see if it has changed anything.
Check it out! the values that were previously not matching, now have matches! that is great. There is another issue now, the line one lookup is matching with the incorrect CustomerCode.
How can we check if it is finding the correct one? lets jump back into the PriceLookup stage and edit the settings once again to view all matches that it is finding.
We have now marked it to show All matches and if save and look at the data
Great, we can see here that the ProductCode CNCAP is found in all three of the customer PriceMapping files we pushed into the collection and they are showing different prices. In short the correct entry is in there, but when we only choose one match, Utopia will find the first match and only use that one. In this case, we getting the incorrect result.
I have set it to Active and also Allow Replacing Documents, because we are testing i would like to post in the same document over and over, so this will help with that. We also want to generate transaction and I have an S3 setup for Maintenance, so I have included that.
In order to choose the correct data we are going to have to match ItemNumber with ProductCode like we have now, but then also match AccountNumber to CustomerCode. If we match both fields then we will get the desired output. If we take a look at the lookup stage there is only space to match a single field, so looks like we are going to have to use a low-code JSON stage here to do a lookup where we match two fields. Jump straight into the
