Discussions

 View Only

Syncing Multiple Tables Between Apps

  • 1.  Syncing Multiple Tables Between Apps

    Posted 08-16-2019 19:01
    Hi,

    For our organization's 'Contacts', we have a few items (tables) that I created: Contacts, Accounts and Locations that are in "Company"

    Relationships:

    Many Locations belong to one Account
    Many Contacts belong to one Account
    Each Account has many Contacts
    Each Account has many Locations

    With how we do proposals, projects and billings, these are our previous relationships from another system. Already setup and easy to do with QB.

    I have apps for specific departments, mainly Sales, Project Management and Billing. Each work differently on data, so when a sale is won, I pass that 'project' via a custom API call. However, how each group (App as of now) works on that project with relationship to contacts, accounts and locations is different, that's why I broke them out into different apps.

    I tried adding a connected table to all 3 in Sales, but it seems to not maintain the relationships.

    My intent is to have the same data, from Company, across all three tables, including relationships, available in other apps. Re-establishing joins into the respective tables within each app (billing, project management, sales) will be up to managers within each department. 

    I've been digging through previous forum posts and searching for the best way to clone these three apps, maintaining their table-to-table relationships, even if it means lag between syncing.

    Background: 

    Sales typically deals with n+1 contacts for bidding (I've seen 10). Sometimes there are multiple accounts and locations, but it's rare. I was asked to try and maintain this. Accounts we use to maintain specifics for dealing with said company, like if it's prevailing wage, non-taxable, etc.

    Project Management would usually deal with separate contacts than the folks bidding, but sometimes the same ones as sales was because it's a direct-to-client project. They are not as concerned with accounts. Sometimes, a single bid/project is spanned across multiple locations.

    Billing is usually a specific person that has a relationship to the Account (ie: accounts payable). 


    What's the best approach on doing this? I figure it's work asking since many folks here have worked with some really complex relationships. Thanks!