I've been struggling with this and when I think I've found a way, I seem to always find a road block. So instead of heading down the wrong road yet again, has anyone done a parent child relationship in Automation Designer? Below is my loop (2 and 3 below just aren't working for me):
- Check to see if an Excel file is already been imported by checking against a file table
- If not already imported, insert a row into the file table and get the FILE_ID from the insert.
- Import the Excel file and use the FILE_ID from #2 to insert in a foreign key column.
What I have tried is:
- The file row insert in an Actionable Query PL/SQL block. If I choose the OUT type, the interface won't let me go any further. Everything grays out.
- Execute Script Text...can't figure out how to map the parameters. I've read examples of how to do with the File-List but that option doesn't appear to be there for text.
- I finally got it working using a SQL Iterator to select from the sequence and then use an Actionable Query to do the file insert. But now I cannot use the FILE_ID in the Import Table Data action as an expression on a column.
Just feel like I must be missing something.
Thanks for any help.