Help to simplify my automation

[FrankieTEWV]

Hello,

Im after some advice please as im struggling with an automation im building.

The bot will receive an excel file with position number, competence name and either a “y” or “n” if that competence needs adding against the position number in our ESR system.

Below is an example of what it will look like

In my collector I'm having to loop through the field names that have a full stop in them (highlighted in purple) and delete them before I add to the work queue, as it won’t allow me to set field names with a “.” in the title in my item data collection in my executor as I get the below error .. so it will fail each time I use the get next item action as the field name isn’t set. I don’t suppose there is anyway around this at all?

Here is what I am doing in the collector

Also the only way I can search for a competence in the ESR system is to hard code in all of the competence  names which are around 47 as I cant pass in a collection field name (in this case the competence name ) into my search action – all I can do is search the position number by using Item Data.Position Number in ESR system and then the only data I have is either y or n  which I cant obviously search for anything with that - so im having to do a decision stage to see if it has y against it – if it does then use the hardcoded competence name to search for it and add it, if it’s a no it moves down and then repeats the decision stage for all 47 competence field names which I think is very messy and must be an easier way.

decision stage

If yes then have to pass in hard coded competence name into enter competence as cant use collection field name

Again any ideas that may make this easier?

Thanks

Frankie

[tim.dauti]

Hi Frankie,

About the dot question, there is in "collection Manipulation" the action "Remove dots from header" (input and output is the same collection).

Did not get the part  "competence" if you provide more ditails would be helpful

All the Best

Tim

[jamie.mcpake]

Hi Frankie, 

For the 2nd part of the question could you use a filter? so filter all the competencies that are marked as Y into another collection and then loop through those to complete your workflow? 

Sorry I might not have understood this right but I am NHS too so feel free to give me a shout via Email or Teams it might be easier via call to talk it out?

Thanks,

[saha1sourav2]

Hello @FrankieTEWV,

It looks like the main issue you're facing is with the dots in the field names. One possible solution is to replace the dots with a placeholder like "_dot_" before adding the fields to the queue. Then, after retrieving the item using Get Next Item, you can replace "_dot_" back with dots to access the correct field names and search for the competence.

[michaeloneil]

Hi @FrankieTEWV 

It looks like you are trying to hard code the column names for the collection before getting the data from excel, is there any reason you cant get the data including the header rows from the excel file? If the excel file headers contain unwanted characters then do a find & replace from the excel extended vbo as you can remove these chartacters before getting the data into the collection. As for the second issue I would use a filter to get all the rows with the relevant position number and competence value is Y.