Writing to an SQLite file where an input is a table from the same SQLite

[joejones-williams]

This is my first workflow and so may be missing something quite simple, however the problem I have is:

I am trying to create a database of sales information (3 tables) from 3 different input files. A new version of the input file is saved each week which is inclusive of the previous weeks' data e.g. week1 = week1 data, week2 = week1+week2 data up to a maximum of 5 weeks (financial period). I have a union of the relevant db table and the new file which flows into a unique tool to remove duplicates and the output writes back into the table (overwrite setting).

I have uploaded the workflow to our Gallery and upon running it have no errors, the workflow reads in the correct files, both db and dynamic inputs, selects unique values and writes back to the db table. I can see that the SQLite file size has increased and has been accessed at the time of running the workflow suggesting that the new data is in the table. However, when I then test this, simple input & browse, it only shows me the 'old' data prior to running the workflow.

Any advice would be massively appreciated.

[BrandonB]

Could you clarify what you mean by "when I then test this, simple input & browse, it only shows me the 'old' data prior to running the workflow."

If a workflow hasn't run yet, how would the SQLite file have the new data? 

In your logs it looks like it has successfully written 5097 records to ReSA_Data, up from the 3736 records that it started with. When this SQLite file is referenced after the workflow has run successfully the first time, it should then be starting with 5097 records, no?

[joejones-williams]

Hi Brandon, 

'In your logs it looks like it has successfully written 5097 records to ReSA_Data, up from the 3736 records that it started with. When this SQLite file is referenced after the workflow has run successfully the first time, it should then be starting with 5097 records, no?'

Is what I'd expect to see yes. So I have 'successfully' run the workflow through gallery and have written 5097 records into the ReSA data table, however, after this 'successful' run, I have created a separate workflow to test the information is present within the table, however only see the starting 3736 records. 

I hope this makes sense?

[BrandonB · Accepted solution]

Ah, my guess is that you have the SQLite file packaged as a dependency. When you open the workflow from server do you see the path on the output to the SQLite file as external? This would be writing the file into a workflow location in the server rather than writing to the original location of the SQLite file. 

[joejones-williams]

Amazing, thank you. Learning now it's best practice to have all file paths as the full UNC path. Adjusted the inputs & outputs and is now working as intended - thanks for your help.