I am stuck once again trying to do somethign that seems very trivial. I have a spreadsheet with several columns. I need to query columns A, B and say E and load that into a table. That is it. So what I built is:
So I need the ability to refresh this data. How I understand it, is I need a file associated with a table that I can unload and reload. So to unload the file (assuming it may already exist) I need the ID of the file to pass to Unimport File. I do not have that - just a name. So I created the branch to the right to list files, get file properties for each file, and a conditional that if the name of the file matches the name I want, then store the Id in a dynamic variable.
Once done I need to first delete this query that got created when the first trial run failed. I set the Crate Query to create a temporary query but now I am stumped - I cannot see the query in the WDesk UI (as per the note on that checkbox), so I cannot manually delete it. Fine - I list queries, iterate and get query metadata and look for the query name in the conditional to get its ID to delete it. But the conditional is failing. I thought that like in normal programming a conditional has a pass and fail branch - so I connected both the red exclamation and the grey checkbox "Link Condition" to try and skip if it does not find the query - but it just fails and stops.
Firstly, I do not think the loop is right. I have Get Query Metadata to iterate over the queries but I think it is iterating alone and the conditional is not applied to each iteration. So then I tried grouping them. That results in the same issue - the conditional is never matched and it fails to delete the query. So the create query call fails with:
Result: Error creating query: duplicate query name. Code 409
See final chain here:
So it seems like List queries will not return queries marked as Temporary - which means it is impossible to use temporary queries as they do not get cleaned up and cause duplicate errors. First issue.
Fine - workaround is to not use temporary queries. For now I just renamed the query. So I got further.
Second issue - it seems like a conditional does not have an ELSE condition. So it is not possible to use the red or grey outcomes to flow out from a conditional if it was NOT met. So I replaced it with a group - the outcome of the conditional is no longer dependent on the ELSE. This seems to work.
Another issue - the conditional on file properties does not work. The Get file Properties return three files, one has a name attribute of "name": "ChainAJEAccounts.csv" and my conditional is:
yet the conditional is never met - the log shows a blank value for all iterations of the variable "file properties.name". This causes future failures.
Lastly - this process seems incredibly inefficient. 8 minutes to run just to import a couple of rows from a spreadsheet into a table? What am I doing wrong / what can I do more efficiently?
Also - this will be much easier if Get query Metadata and Get File Properties supported searching by name and not just ID.