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!

[GeorgeW]

I searched for "IBM" since in the download setup you have to enter an origination address which I believe all start with IBM.

So, I think that found most of them, but I've found a couple that the company code is setup but it doesn't show the IBM number on the spreadsheet (it is in TAM).

So this isn't finding me everything, but closer than I was before.

Thanks!

[Mark]

I saw that too.  You have to look at the structure a bit and search for ones you know for sure are setup.  I saw we had the same company in there multiple times, but the structure was different.  One had the Origination address (IBMxxxxxxx) in the "City" field, while the others didn't.  Don't know if that's from past versions and there have been structure changes or what.  Gotta try to find some consistency, then just let BR7 export those to CSV and go from there. They also use the field position method in this file, too.  But, you can probably ignore the positions a little bit and just use the "contains" operator and have decent results.

I like BR7 because you could build an expression as complicated or simple as needed.  I think that the key is important here.  It looks like they re-use this database.  A record with a Z key holds the origination address in the City field whereas a record with a Y key doesn't reference the the origination address anywhere.  So, you could build and expression based on that information and keep going.  You could even combine those records, into one row, then export.  Like I said: A) very powerful; and B) I often over complicate things 

EDIT: And the "REC" field is what ties everything together.

[Jeff Zylstra]

Good to know.   I have a couple companies that do download through 3rd party programs which I don't believe have the "IBM" account number in the company file setup, but you probably already thought of that.  Glad you got it solved.

[Todd Arnold]

They killed your ability to save to a .DBF file with 2007 (bastards!) but you can still use Excel 2007 and higher to open .DBF files. 

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]

that's what it was.  Thanks, Todd.  I was more on top of this when the change came about, but I've since been using other tools for DBF anyway that made more sense than Excel.

An example of "use it or lose it" information.

[Todd Arnold]

Awesome information in this discussion btw!

[Jeff Zylstra]

Awesome information in this discussion btw!

That it is.  I wasn't aware of the DBF limitation in Excel.  Heck, I wasn't aware that you could save reports as DBF files in TAM either!  I think Hans told me that.  Still not sure I understand the benefits of doing that.  Maybe a good topic for future report posts and/or blogs!

[Mark]

I wasn't aware that you could save reports as DBF files in TAM either!  I think Hans told me that.  Still not sure I understand the benefits of doing that.

Just an example of one benefit randomly generated off the top of my head: direct import to another database program, like a CRM or even something like Crystal Reports, maybe.  No need to convert from one thing to another and to another if dbf is supported.

Want a nightly import of specific prospect data from TAM to a prospecting program?  Use Workspace Macro Pro to fire off a User-Defined search of prospects that outputs to a DBF, then directly imports the DBF file.

I'm sure there are hundreds of other possibilities.

[Hans Manhave]

Awesome information in this discussion btw!

That it is.  I wasn't aware of the DBF limitation in Excel.  Heck, I wasn't aware that you could save reports as DBF files in TAM either!  I think Hans told me that.  Still not sure I understand the benefits of doing that.  Maybe a good topic for future report posts and/or blogs!

The main benefit I have is as a source for further data exploration. Either by TAM Reports itself or for other third party apps. How useful this is depends on what the need (frequency, sharing) and time and skills are of the operator. I got tired of always having to manipulate Excel before I had the info I needed. VLookup is nice, but not always the only solution, for example. Pivot tables are awesome though.

As long as one gets the needed information out of the collection of data, all is well. As long as we have some tool we know how to operate, including hiring someone, progress can be made.

[Mark]

As long as one gets the needed information out of the collection of data, all is well. As long as we have some tool we know how to operate, including hiring someone, progress can be made.

Well said.