Hi there,

I have a question in regards LF SQL database. I want to show an error to the user if the value they enter on the form does not exist in LF repository.  So the user will enter a number, the form lookup rule will check that number to see if it exist on the metadata of the file, if it can’t find it, it will show an error to the user before they submit the form.

We are using Forms to submit a request for information. The user needs to enter a number (the file number) on the form. This number associates with the file in the repository. After they submit the form, a workflow searches this file number in the repository and attaches the form to the file.

When the users forgot to put the file number on the metadata on repository file, the workflow cannot find the file and the submitted form cannot merge with the file. The process fails. They receive an email in this case. However, this is still inconvenient for the user and they still forget to put that value on the metadata, so I must find a way to make the process fool proof. If I have a way to check the file number exists in repository on the metadata field at the time the user is filling the form, we can let the user know with an error message that no file founded in the repository before they even submit the form.

Now, this brings me to this. I’m trying to do this from Lookup rules on forms. I know the field that we are looking for is in dbo.propval table, the field ID is stored in prop_id and the field value is stored in str_val. My problem is I still can’t pinpoint the file. There could be multiple files with the same file number but they stored different folders. Seems like they are all storing in the same table here with different Entry ID’s. With the search repository activity on the workflow designer, I’m easily finding the file that I need but I’m not sure how to the do the same here. When there are multiple entries with the same prop-id and str-val values, I can’t seem to populate my hidden fields since its finding more than one values.

I first thought that I can take the minimum value of tocid id but I don’t know how to program that, not really a coder myself. All I need is to see if prop_id 2650 have the value of ??? on str_val exists in the database from the Lookup rules. If no value, then show the error, if there is a value then continue.

We are using 10.2 and these are just my thoughts for the solution, I’m all ears for any suggestions. Please help me on my issue and let me know if you have any questions.

Thank you so much!