Multiple Database Autofill

[Mike_Kroner1]

Hello All,

We are in a pickle here with autofills as we grow the business by leaps and bounds. The DBA team is creating a separate database for each LOB. What I need to do is set up OnBase to be able to read multiple databases and retrieve member demographics information. 

Currently, we have a database called “PA-ODS” we look at the member ID, Member Name, DOB, and Address.

Starting in 2 months, we will have four new databases for new lobs.

We have a second database called OH-ODS. Again, we look at the member ID, Member Name, DOB, and Address.

The field names are exactly the same; the only thing different is the database name.

How would you create one unity script to capture data from all four databases?

Any help would be greatly appreciated.  

[James_Perry · Accepted solution]

I use PowerShell to create a CSV for my AFKS files. In this case I would query each database and append the results to the CSV. I can then sort and otherwise manipulate the data before saving a final copy to my AFKS folder.

When working with data from multiple sources, ensure that you have unique primary values across all datasets. The member ID should be unique for each row. If they are duplicated on another database that will cause issues. 

You can ask ChatGPT or copilot to "Create a PowerShell script to query 4 databases for member ID, Member Name, DOB, and Address and append the results to a CSV then sort and otherwise manipulate the data before saving a final copy to my AFKS folder."

[James_Perry · Accepted solution]

I use PowerShell to create a CSV for my AFKS files. In this case I would query each database and append the results to the CSV. I can then sort and otherwise manipulate the data before saving a final copy to my AFKS folder.

When working with data from multiple sources, ensure that you have unique primary values across all datasets. The member ID should be unique for each row. If they are duplicated on another database that will cause issues. 

You can ask ChatGPT or copilot to "Create a PowerShell script to query 4 databases for member ID, Member Name, DOB, and Address and append the results to a CSV then sort and otherwise manipulate the data before saving a final copy to my AFKS folder."

[Thomas_Reu · Accepted solution]

I would create a view that looks across all the databases and gets all the data - probably 4 unions in your case.  Then I would point my autofill to this view.  If this runs too slow, then I would use a a scheduled powershell script to dump the view to a local table and I would point the autofill to that table.

[sskupien]

@Tom @Jim Perry   As a Hyland Community Manager, thank you for all the help you offer in the forums! Much appreciated!

[James_Perry]

@Scoop Skupien I have received so much help, guidance, and insight from the community. I am grateful and enjoy giving back where I can. I am surprised by the number of organizations that do not use the community as the fantastic resource that it is.