Company File Interface setup - database

[GeorgeW]

Apparently you cannot run any reports on the Company File Interface setup area of TAM?  If not, can anyone tell me the name of the database this information is kept in?
Thanks

[Mark]

You might be right.  Don't' know what you're looking to report on so this may now be what you need, but if you go to Search -> Search - Files -> Companies you can get some info.

[Jim Jensen]

I don't know which file it is, but it may be in the APPS folder instead of WINTAM or TAM.

[Mark]

Wait, you said company file interface and I realize what I posted is just for company file, so I don't think it will help.

[GeorgeW]

Yes, company file interface. I would like to know all the companies we have currently setup with download information since most of it was setup well before I started working here 5 years ago.  I don't want to have to manually go through hundreds of company codes to find out this info. Since we put the info in, there must be a way to get the info out!! 

[Mark]

According to Filemon, the three DBF's accessed when opening Company File Interface are:

• \APPS\APCOPR.DBF
• \APPS\APSAFLDS.DBF
• \TAM\INS.DBF

With the majority of the activity on the first one.  That could get you started, but I'd copy those files before even touching them and then only touch the copies, of course.

[Jeff Zylstra]

According to Filemon, the three DBF's accessed when opening Company File Interface are:

• \APPS\APCOPR.DBF
• \APPS\APSAFLDS.DBF
• \TAM\INS.DBF

With the majority of the activity on the first one.  That could get you started, but I'd copy those files before even touching them and then only touch the copies, of course.

An excellent suggestion, Mark.  You should be able to open these files using Excel or even a MS Access (if you know how to run reports out of that).  I can never figure out Access, so I would be more inclined to open the COPY of the DBF file Excel, and then sort the field in question to point out all of those companies that are set up for download.  Quick and dirty with a side of ugly thrown in for good measure, but it's the best I can do!   

[Jim Jensen]

If you can go by the company unique coverage code files, those are all in /APPS/TXT and start with COV. The next 2 letters are the policy type, WA=Watercraft, HO = Home, AU=Auto. The last 3 letters are your TAM company code. So the file for personal auto for Indiana Insurance for us is COVAUIND.TXT. if you pick one of the policy types used most, like personal auto, you can simply look for all of the COVAU*.TXT files, then see what company codes are in that list of files.

[GeorgeW]

Good idea Jim, unfortunately, I know they never entered all the company unique codes! 

[Mark]

Didn't they remove .DBF support from Excel 2007?

There are plenty of other DBF utilities out there if you cant open in Excel.

[Mark]

Good idea Jim, unfortunately, I know they never entered all the company unique codes!

I'm not done helping you yet   Just need a minute to gather my plan of attack and think I might have a solution - or near solution for you, Sir.  That is, if this works the way I expect it to, lol.

[Mark]

Ok, so, download the Free BR7 utility.  Open the COPY of APCOPR.DBF and see if you can figure out how to find the info you're looking for.  Once you think you have a handle on the structure, choose export from the drop down menu on the right and you can filter the data so it only exports what you want to CSV or just Excel.  Then you can do the rest in Excel.

BR7 is free, but is included in a tool I use regularly called GoldBox.  I use Goldbox to export, import, manipulate DBF and SQL data and also to automate those tasks, so I'm quite familiar with these scenarios.  I just don't have time to try and figure out how to determine what's setup for download and what's not.

You can skip the filtering in BR7 and just use 1=1 so that it exports everything to Excel if you're more comfortable with that.  If you can just open in Excel, then you can even skip the BR7 thing, but the power in that would be to have BR7 export you the cleanest Excel file possible to make it easier to work with.

That's about as much as I have time for.  Hope it is helpful.

[GeorgeW]

Thanks for all the help!!

I opened a copy of APCOPR.DBF  in excel and believe I found the info I was looking for. Looks like we have 62 company codes setup with download information.

Again, thanks!!   

[Mark]

I looked at this one more time because it was bothering me and I think you'd have to determine what data is always there for the companies that you download with and then use an expression that exports records that contain the data you're looking for.

The Expression builder is quite powerful if you look at the options it has.  For example, if you want to export every company that's setup to download Umbrella, Fire, & Boat Policies, build an expression that exports records where the "Contract" field contains UMBRFIREBOAT.  That Expression would look like this:

Code Select Expand

"UMBRFIREBOAT"$CONTRACT

[Mark]

Thanks for all the help!!

I opened a copy of APCOPR.DBF  in excel and believe I found the info I was looking for. Looks like we have 62 company codes setup with download information.

Again, thanks!!   

I have a tendency to over complicate things sometimes!  Glad I could help!