I have created a small project on Mockaroo. In my project I’m creating mock supplier data:
KEY, COMPANY_CODE, NAME, CODE, REGISTRATION_NUMBER, DUNS_NUMBER, STREET, CITY, COUNTRY_CODE, COUNTRY_NAME.
All pretty straight forward. The only thing is that I have written a small formula to generate a CODE from the NAME field. This is call in the CODE field:
codeFromName.call(field('NAME')).upcase()
Now I’m running into an issue since the generated COUNTRY_CODE is only 2 characters and I require it to be 3 characters. As a solution I’ve uploaded a CSV file which contains both 2 character and 3 character country codes. I have split the COUNTRY_CODE column into 2 columns: COUNTRY_CODE2 and COUNTRY_CODE3. COUNTRY_CODE2 is the 2 character country code and COUNTRY_CODE3 should be filled from the dataset with the 3 character country code. For this I’m trying to use the formula:
from_dataset("Countries", "alpha-3", alpha-2: COUNTRY_CODE2)
For some reason this gives an error which also includes the codeFromName formula I’ve created:
Syntax error in formula 'codeFromName = lambda do |name| nameParts = name.split(%r{[-'\s]}) nameParts.delete("and") partLength = 3 case nameParts.length when 1 partLength = 4 when 2 partLength = 2 else partLength = 1 end code = "" for namePart in nameParts code << namePart[0..partLength] end code end from_dataset("Countries", "alpha-3", alpha-2: COUNTRY_CODE2)'
How can I fix this issue?
I have found a solution. It seems there was an error in the dataset lookup. It should look like this:
from_dataset("Countries", "alpha-3", "alpha-2": field('COUNTRY_CODE2'))
The error message could be clearer though and not include the codeFromName formula