What is the best way to populate LF List Field data from a SQL table? Customer has approximately 1000 -1500 street names he would like to populate.
Another option is to configure the that table as an external table in Laserfiche. Then taking your Laserfiche field, make it a text field and set it up as a dynamic field that maps to the specific column in that external table. You don't need to configure any child field dependencies. Then in Laserfiche, that field will appear as a standard list field, but will have its values coming directly from the SQL table.
Hi Ali,
The easiest way to populate your list fields would be to take advantage of Workflow's database look up capabilities. The activity you will want is Query Data. With this activity you can grab information out of your SQL table, and populate your fields based on specified criteria.
Hi Ali,
If your question has been answered, please click the "This answered my question" button on the response.
If you still need assistance with this matter, just update this thread. Thanks!
Hi Ali or Jason,
I am aware of the External Table capability. However, it has to be linked to SQL or Otacle. Since I have an Excel document I was thinking of using ODBC.
Is it any possibility to have a ODBC connection and populate the list at the beginning of the Workflow using Query Data? The list will always be empty to be populated using Workflow Activities. Is it something achievable? I have tried this method unsuccessfully.
Cheers,
The list field is per template. From your description it sounds like your workflow would write values to it for each document processed. Not to mention that if multiple documents would try to access the spreadsheet at the same time, Workflow would fail because Excel does not support multiple connections.
The Append List Field Choices activity can add values to a list fields, but it will not remove any values.
Why not just import the Excel spreadsheet into a SQL table and use it as an external table? If this spreadsheet changes regularly, a scheduled import job can be created in SQL to periodically update the table.
Hi Minura,
Thanks for your reply.
This list is dynamic and it's managed by the client. Some clients are reluctant to provide access to SQL server to more users. Not to mention that to someone not familiarized with SQL, the interface may look too complicated. That's why I was trying to give them access to an EXCEL file where they can easily update content to be reflected in this dopdown list.
I will see what i can do with the "Append List Field Choices" activity.
Thanks for your answer
Right, I was suggesting you keep the Excel document since your users would find it easier to edit.
I guess you could have scheduled workflow that could delete all the field values and then append the new ones. But if this is anything more than a few values, a direct SQL Import job will be a lot more efficient.
A SQL import wouldn't require allowing access to more users, it could run as the same user Workflow would use in its data source.