Author Topic: 1 FM Database ->Many QB Files  (Read 1249 times)

bcooney

  • Member
  • *
  • Posts: 2
1 FM Database ->Many QB Files
« on: September 26, 2013, 07:09:45 AM »
Just starting a project for a design firm that has separate QB companies, but shares Customer info (and perhaps more) and would rather have one FM solution shared by multiple companies (just using Security and filters to narrow the records they view).

However, I'm concerned that I cannot post conditionally by company to one of several QB files. I welcome any input and advice.

-Barbara

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: 1 FM Database ->Many QB Files
« Reply #1 on: September 26, 2013, 07:47:59 AM »
You're jumping into the deep end.

It's possible to accomplish your goal, but there's a lot of overhead to making sure the users cannot push a Customer, a Transaction, etc. to the wrong QB Company file. The scripts that interact with QB will all need to start by confirming that they're interacting with the right file, or by launching the right file in "silent" mode. If a user is logged into a QB Company file, that's the only QB file the user can interact with until that file is exited. If you're looking for "real time" interaction (vs. batch processing) across multiple QB Company files, the user must be logged out of QB and each interaction will start and end by opening and closing a silent connection (in which the QB file is launched but the UI is not available to the user) to the targeted file. There's a lot of overhead to opening and closing connections to a QB file that could slow down your user's experience, and launching in silent mode increases the overhead.

If you find an acceptable compromise for how/when to interact with QB, you'll need a way to store multiple QB ListID values for each Customer/vendor/Item/etc. that appears in more than one QB Company file. Are you using multiple FMP transaction tables (Invoices, for example) or do all Invoice records live in the same table? If the latter, I presume there's a field that stores a value that differentiates one Company from another and that more or less corresponds to a QB Company file. I'd probably create a table that has the Customer primary key value, the QB ListID value, and a field whose value corresponds to whatever value is in the Invoice table that identifies the Company that "owns" each Invoice. This would let you lookup the correct QB ListID value when posting an Invoice.

It's possible to create a single set of scripts that will work across all QB Company files, but as implied at the outset, this is a pretty deep pool and adds significant overhead to your integration.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

bcooney

  • Member
  • *
  • Posts: 2
Re: 1 FM Database ->Many QB Files
« Reply #2 on: September 26, 2013, 11:32:04 AM »
Thank you so much for such a thorough reply.

As you've suggested, I could create join tables for the fact that an entity might have more than one QB ListID (as you've described CustID, QBList, CompanyID). But, you have me thinking about "how do they handle that now?" That is, apparently, they must have redundant data across their companies. To me, all the more reason to unite this data properly in FM.

And so, yes, I would like all the Invoices in one table.

I want to give Acctng. a lot of control over the QB process (not looking for much in the background or "silent" mode).