Question on GUID-like number

SELECTED ANSWER

replied on December 18, 2017

Hi Raul,
This is the last bit query that I could find from our old process. I think this should give you the resume_id and assigned user.

SELECT TOP (1000) MI.bp_instance_id
      ,[start_date]
      ,MI.status
      ,[title]
      ,[bp_name]
	  ,WI.instance_id
	  ,WIR.worker_instance_id
	  ,IA.resume_id
	  ,US.displayname
	  ,US.user_id
	  ,US.email
  FROM [LFForms].[dbo].[cf_bp_main_instances] MI
  join [LFForms].[dbo].[cf_bp_worker_instances] WI on WI.bp_instance_id = MI.bp_instance_id
  join [LFForms].[dbo].[cf_bp_worker_instnc_to_resume] WIR on WIR.worker_instance_id = WI.instance_id
  join [LFForms].[dbo].[cf_bp_instance_approvers] IA on IA.resume_id = WIR.resume_id
  join [LFForms].[dbo].[cf_user_snapshot] US on US.id = IA.user_snapshot_id

1 0

replied on November 16, 2017

Hi Raul,

I imagine you are trying to construct the link to send your own form notifications?
We had a workflow setup to do this but dropped it (quickly) after the Notification Service was introduced in forms. Our notification workflow did work well for us while we used it.

You can get the number(s), the resume_id, from a query into the forms database using the bp_instance_id.
If a form has more than one task assigned you will get a resume_id for each task.

SELECT resume_id
  FROM [LFForms].[dbo].[cf_submissions] as SUB
  join [LFForms].[dbo].[cf_bp_worker_instnc_to_resume] as WRKINST on WRKINST.last_submission_id = SUB.submission_id
  where bp_instance_id = XXXXX

3 0

View 3 previous replies

replied on November 17, 2017

Thanks Andrew,

Yes, we're storing information into a custom database table through out an approval process. During each task we're storing general information such as title, date, names and all of that.

We're also storing the step in which the form is sitting.

What we're trying to do is run reports against the database table so that it could also include the link to the form, so that a person could click on it and just open the form and continue.

These people don't have Process Admin access, so creating reports using Forms did not work for us because the results are not clickable.

I'll give your code a test and see if I can make it work using your approach. Will keep you posted.

Thank you,

Raul Gonzalez

0 0

replied on November 17, 2017

Andew, this worked really nice; thanks!

I had to do this using an Custom Query activity in Workflow and my code was just slightly adjusted as:

SELECT resume_id AS resKey FROM cf_bp_worker_instnc_to_resume INNER JOIN cf_submissions on cf_submissions.submission_id=cf_bp_worker_instnc_to_resume.last_submission_id AND bp_instance_id = XXXX

Thanks again for the tip. This will get me a long way.

Thank you,

Raul Gonzalez

0 0

replied on December 12, 2017

Hi Andrew,

Would you happen to know where I could grab the person whose task is assigned too?

It wouldn't be in any of those tables right?

Thank you,

Raul Gonzalez

0 0

SELECTED ANSWER

replied on December 18, 2017

Hi Raul,
This is the last bit query that I could find from our old process. I think this should give you the resume_id and assigned user.

SELECT TOP (1000) MI.bp_instance_id
      ,[start_date]
      ,MI.status
      ,[title]
      ,[bp_name]
	  ,WI.instance_id
	  ,WIR.worker_instance_id
	  ,IA.resume_id
	  ,US.displayname
	  ,US.user_id
	  ,US.email
  FROM [LFForms].[dbo].[cf_bp_main_instances] MI
  join [LFForms].[dbo].[cf_bp_worker_instances] WI on WI.bp_instance_id = MI.bp_instance_id
  join [LFForms].[dbo].[cf_bp_worker_instnc_to_resume] WIR on WIR.worker_instance_id = WI.instance_id
  join [LFForms].[dbo].[cf_bp_instance_approvers] IA on IA.resume_id = WIR.resume_id
  join [LFForms].[dbo].[cf_user_snapshot] US on US.id = IA.user_snapshot_id

1 0

replied on February 5, 2018

Andrew,

This is working great for what we want to pull. We have a bunch of records in a custom database table and based on that, we generate the links.

We have a column for "businessProcessID" that we use for joining your query, but some of the rows have a Null value for our "businessProcessID".

We however have the Entry ID for all those records. I was wondering if the "buisnessProcessID" could be populated using the Entry ID. Otherwise, without the busienssProcessID we can't generate all the URLs.

Thanks again Andrew,

Raul Gonzalez

0 0

replied on February 5, 2018

Also, in one of the links, when I click on it, for some reason I get this error:

"This task has been locked because a submission has already occurred but hasn't been processed."

I'm wondering what that is. I can't find the reason for it to locked.

0 0

Question

Question

Answer

Replies

Sign in to reply to this post.