You are viewing limited content. For full access, please sign in.

Question

Question

Use forms database in field lookup

asked on June 13, 2018

I didn't see anything related to my question, so I'm hoping someone has done this before and can assist.  Can I do a lookup against the forms database to fill field content?

Use Case:  I will have three forms

  1. create an event form (will capture event name, date,  location, session(s), session start/end time(s),etc.)
  2. sign-up form completed by users to capture user demographics (will do lookup against HRExtract for this); will also do lookup to capture event information and provide session options in drop down for selection.
  3. generate rosters from user sign-up information (will need to do lookup to extract all users who signed up based on the session times they selected)

What I need to know is, if I capture the instance ID of the initial event creation form, can I:

  1. For SIGN-UP Form:  Do a lookup against the forms database to populate the event information in the sign-up form (all single line fields except for sessions), including populating a drop down with session information that was captured as part of a collection on the Create Event form?
  2. For ROSTER GENERATION Form:  do a lookup against the forms database using the Event ID (this number would be the instance ID from the Create Event Form which would be a hidden/read only field on the Sign-up form identified as EVENT ID)  The Event ID would query forms database to lookup and populate the event information (similar to #1 above but without the session information).
    1. The Event ID would also be used in a Workflow in the Business Process to query  forms database to obtain ALL users who signed-up for the event including their session selections.  This Workflow would then populate a Word or PDF doc (one per session) showing all those who signed-up for the event.

 

Looking for some guidance, since I would typically create additional SQL tables for the Event and Sign-up information using a Workflow in the Business Process vs. querying the forms database directly.

Thanks!

0 0

Answer

SELECTED ANSWER
replied on June 14, 2018 Show version history

Hi, I have done something like this on a smaller scale.  Just like you mentioned, the easiest way to do it is to create a SQL View (instead of tables) that contains all the information you want to query, because of how Forms stores information in its tables.

cf_bp_data will have all form data, unique to each submission by submission_id, then you have to match submission_id by using the cf_submissions table which will give you the submission_id and the bp_instance_id.

If I may suggest - built two separate SQL views to make it easier to lookup and cleaner to troubleshoot if you need to.

 

Does that help?

 

0 0
replied on August 21, 2019

Hey Lidija,

I'm trying to do something similar to this.  Would you mind sharing what your views look like?  I don't have much experience navigating through the backend maze and I think looking at them would be very helpful!

 

Thanks...

0 0
replied on September 23, 2019

Hello Jamie and Lidija,

 

Same here, looking to do something similar. 

Jamie, did you manage to achieve it ?

Thanks

Veronique

0 0
replied on September 24, 2019

Veronique,

I've not come across any more information.  I've moved on to other projects.  I will probably circle back at some point, though.

 

Thanks...

 

Jamie

 

0 0
replied on October 1, 2019

Thanks Jamie for your reply

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.