Question

Update Data with Dynamic Columns

Workflow

Updated April 11, 2017

asked on April 5, 2017

I want to include a dynamic column name in an update. The simple Update Data activity will not work, because it does not allow me to dynamically change the column based on the data.

Basically, I request a form from all employees every month. I want to record the form they submit in the appropriate column for that month. So, if someone submits for January, I want to mark it in the January column, February in the February column, etc.

I've written a custom query and can get it to work fine when I test it by manually inputting data, but when I pull the same data from my PDF form, I get no results.

On this query I get 1 row(s) affected.

When I actually run it, my track tokens returns no results

What am I missing here? Thank you for any insight.

0 0

Answer

SELECTED ANSWER

replied on April 10, 2017

I think the problem is when you're building the dynamic query - specifying a column name based on the PDF field value and using a parameter to try and do so.

Instead, I tried an approach where I build the SQL statement in a token while still specifying the month dynamically name from a value in the PDF, like so:

The custom query SQL is then just the token, in my case I creatively named it sql so it looks like:

Note, in the SQL I built and placed in my token, I still have the parameter specified, so I'm still updating that in the Custom Query activity.

I feel like I've committed both Laserfiche and SQL sins with this approach, but it finally did work for me.

2 0

Replies

replied on April 7, 2017

Could the Retrieve PDF Form Content be padding your employee name with extra spaces? Can you run a test by changing your query to specify an employee and only update the month column from the value in the PDF?

Like: UPDATE dbo.TS_DriverTEST SET @Month = 'Submitted' WHERE Employee = 'Allison Bateman';

0 0

View 5 previous replies

replied on April 10, 2017 • Show version history

The PDF form has a drop down to ensure that employee names are exact. It's not a free-form text field. I've not had problems before with this same field before, and I pull from it for other purposes.

0 0

replied on April 10, 2017

What field type is Employee in your SQL table?

0 0

replied on April 10, 2017

You got me intrigued so I setup an workflow and SQL table that's (I think) similar to yours. Sure enough, those tokens say 0 for the count and false for the results found, but (!!) my SQL table was updated successfully anyway.

0 0

replied on April 10, 2017

Hmm, the Employee field is nvarchar.

I even tried to assign the PDF fields to the template and then pull from there and no luck. It will not update my SQL table. Everything else works fine. I'm not sure what I'm doing wrong here.

0 0

replied on April 10, 2017

Does your workflow run without error?

There are records in that table for the employee you're testing on, right?

0 0

replied on April 10, 2017

Yes and yes. It runs without error. This is it in context of the workflow that I put my test into. It's now called the Record Receipt of TS - TS is the form that I want to record has been submitted. Everything else works.

My Custom query with @Month = Month and Employee =@Name

My token return finding Allison Bateman and February

My SQL Table with Employee Column and Month Column

0 0

SELECTED ANSWER

replied on April 10, 2017

I think the problem is when you're building the dynamic query - specifying a column name based on the PDF field value and using a parameter to try and do so.

Instead, I tried an approach where I build the SQL statement in a token while still specifying the month dynamically name from a value in the PDF, like so:

The custom query SQL is then just the token, in my case I creatively named it sql so it looks like:

Note, in the SQL I built and placed in my token, I still have the parameter specified, so I'm still updating that in the Custom Query activity.

I feel like I've committed both Laserfiche and SQL sins with this approach, but it finally did work for me.

2 0

replied on April 11, 2017

Andrew, you are AWESOME! I test that and it hates me within workflow, but when I actually run it, it works!!! You are a genius!

I was pretty sure it had something to do with my dynamic column selection. That's an awesome work around. Thank you very much.

0 0

You are not allowed to follow up in this post.

Question

Question

Update Data with Dynamic Columns

Answer

Replies

Sign in to reply to this post.