How can I get values for a field from Forms database based on an instance id?
What specifically are you trying to accomplish?
Although it is technically possible to piece that data together with a query directly to the database, it is not usually recommended; hits to the database could impact performance, changes to the form fields or updates to Forms could break the query, etc.
Sometimes it is the only way, but first I would suggest looking at other options:
Because of how customizable everything is, things like form or metadata fields are fairly abstract in the application databases, even more so with Forms because tracks it values at different stages of the business process, so it requires piecing together data from several different tables.
I agree with Jason, it really depends on what you are doing and I have seen setups where collected values are being pulled from the forms DB directly to populate other things and it causes problems.
If you are just needing to look for something for troubleshooting, values are in [LF_Forms].[dbo].[cf_bp_data] and instance and submission ID are in [LF_Forms].[dbo].[cf_submissions]. You can join them on the submission field and see what was collected from that instance.
If you are needing to use the data for something bigger than just a verification or finding a problem, both of Jason's suggestions above are great.
Thank you, all. We are trying to provide access to data from Process A to other processes. So, was thinking of building a view/stored procedure to look at Forms database for data from Process A. So, other processes can query that view/stored procedure to get data.
If there are just some select data points you need from one process to be visible in the other - I would highly recommend setting up a database table that is dedicated to that purpose. Have a workflow that is called in the process to save those values into the database table where it can be accessible to the other process. If needed you can have it insert the record to the database at the start of the forms process, and then do updates to it as the instance progresses. As Jason mentioned, this is going to be specific to your needs, and not at risk of issues with future changes in Forms.
I definitely wouldn't recommend creating a view or a stored procedure for something like that because a lot could go wrong.
In situations like that we always go with the second option I presented.
The only time I've ever gone directly to the Forms database is for extreme troubleshooting, or for SQL jobs that only hit the db once a day/week and copy data out to a separate database.
Thank you