Question

Auto-fill Template Fields based on conditions or type of information

Laserfiche Scanning

Updated November 27

asked on November 26 • Show version history

Hi everyone,

As part of a project I am carrying out for a large volume of documents that will be scanned next week, as I explained in this post where I asked for help with one of my dilemmas in this process. Now I have a request from the client that I cannot find how to achieve in Laserfiche, perhaps one of you can shed some light on this for me.

Scenario:
The client has asked me that due to a fairly large amount of information that is going to be scanned, he wants to avoid, prevent and/or minimize the greatest amount of errors that could occur when the Clerks who scan the documents can commit when writing sensitive information in the fields of the template associated with the document, for this reason, the client has given me a database (excel document) that has three main columns with information: Business Name, Business Address and PIN Number, and wants to see if it is possible to Autofill the fields of the template with that information and that based on certain conditions it shows the corresponding information already pre-loaded in the related list box, to cite an example:
If the client chooses from a Business Name list the business "Pink Bakery" automatically put in the Business Address field the address related to that business, and in turn in another list Autofill with the data of the associated PIN Numbers, so that they can choose between those PIN Numbers which one corresponds to the document being scanned. scanning.

The Excel document contains the details that a business at the same address can have one or more PIN numbers.

I wonder the following:

Is there a way to fill in these fields of the templates with the information from the Excel or by creating a database or something with that information, so that it is possible to auto-fill the associated fields?
Is it possible to show values based on conditions applied to the fields of the templates? where if the business is selected it shows the address data and associated PIN numbers or if the address is selected it shows the name of the business and the associated PIN numbers.

I don't know if I have been able to explain myself up to here, any help in this regard would be very grateful.

PS: I attached an example of the Excel file I have. There are more record in the original file.

Business-Address-List.xlsx (9.41 KB)

| Download

0 0

Answer

SELECTED ANSWER

replied on November 26 • Show version history

Yup, Dynamic Fields is the way to go.

1. Import your spreadsheet into a SQL table, for ease make sure to use the same SQL instance as the LF DB.

2. Connect your SQL table to the external tables VIA the admin Console

3. Configure the Dynamic Table on the template, Example below:

Here Is what my test SQL data looks like:

Here is what it looks like in action from the repository:

Good Luck!

4 0

Replies

replied on November 26

I think you should be able to achieve what you want by importing the Excel data in to SQL. Then using the SQL table as an External Table, and setting up Dynamic fields in the metadata template.

Below is an example of a template we use with Dynamic fields...

1 0

View 6 previous replies

replied on November 26

Hi Jonathan, thank you for thanks for your reply, what you're saying is interesting. Let me testing. How do I connect to an external database or table? I've already converted Excel to SQL.

Thank you

0 0

SELECTED ANSWER

replied on November 26 • Show version history

Yup, Dynamic Fields is the way to go.

1. Import your spreadsheet into a SQL table, for ease make sure to use the same SQL instance as the LF DB.

2. Connect your SQL table to the external tables VIA the admin Console

3. Configure the Dynamic Table on the template, Example below:

Here Is what my test SQL data looks like:

Here is what it looks like in action from the repository:

Good Luck!

4 0

replied on November 26

Wow this has been very enlightening, thank you very much, let me check it out and see how I import the SQL table to the server or to the LF SQL instance itself, and then connect it, that's where I'm a bit stuck right now as I haven't been able to do it.
Thank you very much, I'll keep you posted on the progress. But if you have any guide or illustration on how to import the table into SQL and connect it to the dynamic fields it would be great to have it.

0 0

replied on November 26

Absolutely!

There are many different ways of importing your spreadsheet into SQL.

Below are 3 examples.

The size of your dataset will dictate the best way to import your data.

1. There is an import wizard in Excel

2. There is an Import/export utility included with SQL

3. Copy & Paste using SQL Management Studio

For this example, I'll show #3.

#3 - Assume your Excel sheet is formatted with the same column order as your SQL table.

Ex.

1. Design the table in SQL:

2. Your Excel example you provided needs to be reformated similar to the bottom section:

3. Copy from Excel

4. Edit your table in SQL Management Studio and Paste the Excel data into the table. It's super easy when you have small datasets.

End result should be something like the below:

2 0

replied on November 26

Troy,

All the information and guidance you have provided me has been very accurate and precise. I have followed everything you explained to me to the letter and I have been able to solve the problem satisfactorily. There are only a few details left to fix errors that I discovered when normalizing the data in Excel, but that is something that the client must resolve, in fixing the information and providing it without errors, so that this process is as professional as possible.

I have no words to express my gratitude for having taken part of your time to explain everything to me in detail. In addition, with this example you have not only helped me with this problem, but also with the question of how to connect to an external database in SQL and exchange data to and from it as support for cases like this that is sometimes necessary.

I reiterate my gratitude for your help.

I have to see what part of your answers I marked as a solution because all your answers were a lesson.

Thank you!

0 0

replied on November 26

Troy, I have a question related to this, considering that all this data would be in a table in the database, how could I solve the case that when scanning a document, the name of the related Business, as well as the address and the pin number do not exist in the database, how could I insert it so that it can be used? Is there any way to add them? Or is it necessary to add an additional pin number to the business and address?

Is there any way to do this within the Laserfiche ecosystem or from the same template when entering the information, so that you do not have to go to the database manually and insert it?

Thank you

0 0

replied on November 27

Hi Luis,

Happy to help.

I typically build a Laserfiche Maintenance form to Add, Modify, and Delete records as needed on the SQL table.

Keep in mind this does require forms professional version of Laserfiche forms to accomplish the lookups needed.

Also, while a maintenance form process is great for low-volume changes, if there are a large number of changes, it's always faster / better to replace the data using one of the previously discussed methods.

Good Luck!

1 0

replied on November 27

Good morning Troy, Thank you, I am reviewing related documentation to see if I can perform this type of editing, adding or deleting records in the SQL DB, for this type of case.

Thank you for your help and time, have a wonderful day and Thanksgiving

0 0

replied on November 27

Great, Same to you.

Happy Holidays!

0 0

You are not allowed to follow up in this post.

Question

Question

Auto-fill Template Fields based on conditions or type of information

Answer

Replies

Sign in to reply to this post.