Hi All, Is it possible to create a UserEvent script that checks the integrity of a data import line by line while the data is being imported - and stop the import if there's an issue with the data? Had anyone done this and have an example script that they could share? TIA

How are you importing this data? CSV? REST? Map/Reduce?

Via a CSV.. using the journal import.

If the CSV is importing several Journals, then no, you can't stop the creation of all of them because an error on an individual Journal. If lines 1-2 create a journal, and lines 3-4 create another journal, the first one will be created if the data is correct.

The CSVs only create one journal. I only want to stop the creation of the journal that has incorrect data anyway.. so it would be ok if in your example the first journal was created and then the second would fail. Do you have an example or pointers on how I can achieve this?

The CSV Import of a single JE will stop if a single line has incorrect data. And by incorrect data I mean: missing mandatory fields, using incompatible subsidiary, department, currency combinations, etc... incorrect data types, non-existing entities, etc.

I need a script to validate data... on top of the incorrect data scenario's you mention - which will be rejected by default. I need to validate against rules to ensure the quality of the data.

Oks, then you need a User Event Script than runs beforeSubmit, in the context of CSV Imports The script checks the newRecord object (the Journal before save), validate what you need, and return throw an error to avoid the save operation.

This ^ But to be annoyingly pedantic, you need to throw an error, not return one.

When you choose import journals it doesn't give the option to allow server-side scripts to run... should I disable this option so users import a different way?

there's an option to run serverside scripts on all CSV imports?

I think that defaults to running the scripts/wf

if you're doing manual approval you can certainly run your validation there I suppose, my general rule is to catch bad data asap and report back to the user, so it feels a little gross

Some JE can be massive, and thats why the Import options is better than using the UI Waiting till approval to send an error is somewhat weird because the JE is already created

I'd certainly prefer to catch before the JE is created, then the importer will have to fix and should teach them not to make the errors in the first place. At the moment, the administrator, is constantly having to make corrections, because the approver doesn't even check for data errors. It sounds good that Financial > Make Journal Entries > Import possibly defaults to running scripts. What I need now is an example script that I can test to see if that is the case, does anyone have a script that will do something to a journal on import?

Something along the lines:

var journalObject = context.newRecord;

var lineCount = journalObject.getLineCount('line');

for (var i = 0; i < lineCount; i++) {

var accountId = journalObject.getSublistValue({sublistId: 'line',fieldId: 'account',line: i}); //grab a field

//validate the field

}

Thanks, I've manage to get a script that loops through lines and grab the account id... now to add the dozen or so validate rules I need.

Wow sounds like a lot of validation. We validate if the Department is the correct one, and nothing else.

I have to validate that all non balance sheet accounts have a contract assigned and then that it's the correct type of contract for the account.