6 Replies Latest reply on Feb 14, 2012 10:51 AM by MarcMcCall

    Stuck on relationship

    MarcMcCall

      Title

      Stuck on relationship

      Post

      I'm trying to get a relationship set right so I can choose an address for a Contact.  I have 3 tables. Accounts, Contacts, Addresses.  The Acounts and Contacts Are related through an ID_account. The addresses are tied to the Account through ID_account.  Some how I need to have a selection box on the Contacts so I can select an address from the addresses table, that have the same Account relationship.

        • 1. Re: Stuck on relationship
          philmodjunk

          Does the account have an address or does the contact have an address?

          Can a contact have more than one address?

          Can more than one contact have teh same address?

          • 2. Re: Stuck on relationship
            MarcMcCall

            Yes the Account has an address, but It is imputed on the account table.  The contact is only going to have on address, but more than one contact will have the same address.  I'm still stuck on choosing the address description from my other post.  The dropdown value list I created worked great when I only had the first account set up. As I set another account up and imported more addresses linked to the second account, when I was selecting addresses for the contacts under the second account all of the addresses from both were showing up in my drop down list.

            • 3. Re: Stuck on relationship
              philmodjunk

              I'm not sure I have a clear picture. The acount has an address and the contact has an address that may be different from the accout?

              Can your contact have more than one address?

              • 4. Re: Stuck on relationship
                MarcMcCall

                 

                 

                Yes.

                I'll expain the whole process.

                 

                1. I use a supplied .XLS template from the webstore company to fill in Account information.

                gender name dob company_name tel fax mob email password internal_account receive_newsletter address_description address address1 city zip state_code country_code customer_type

                This is how I create multiple accounts on the webstore.  I can use an .XLS up to 1mb in size.

                 

                2. Then I have to open one Account at a time and upload a provided .XLS template for addresses.

                address_description address address2 city zip state country_code

                 

                3. Then under the same Account I upload Contacts.

                gender name dob company_name address_description tel fax mob email password recieve_newsletter allow_orders allow_department allow_cost_centre allow_addresses customer_type administrator

                 

                I have to upload addresses FIRST...... A must, the way the webstore is designed.... That way when I upload contacts, it only has a field for address_description.  The webstore uses that description to put a default shipping to the contact.  

                 

                Now, the reason I want to figure this out, our back office sytem uses 2 tables, Accounts and adresses.  But here is a tricky part.  The address table is somehow devided by shipping, and contacts.  I do not have accsess to the backend of the software.  The company that developed it is a pain in the butt.. When I export I have to export Shipping addresses in one .XLS and the Contacts in another .XLS.  They must be flagged some how as a contact or shipping.  But like I said I don't have access to the back end.  So this is where I am trying to figure out how to be able to choose one of the address descriptions from the addresses I created.  If I just use the address that exports out with the contact, I may have over 1000 adresses, which I dont want because there will be so many duplicates and when a customer checks out they will have to choose one out of a list of 1000.  Plus the addresses from the address table have the "care of" info, or the "Attn: to" info already there, and when someone places an order on the store, we had a custom utility program built for us that uses the address to match it to populate a sales order on our office system.  My whole reason for building this program, is to save time filling in the web store provides templates.  Just import the one from the office DB and Export into one for the webstore.  it makes all kind of calculations, like concatenating names because the webstore only has one name field an we have 2.  Doing Proper formulas and Lower formulas for email.  We have over 50k Accounts with easy over 100K contacts and addresses.  Using excell to do all the formulas would take forever.  I hope that helps..

                • 5. Re: Stuck on relationship
                  philmodjunk

                  Are you trying to replace the current back end that is so difficult to work with with tables of your own design or are you trying to find a way to work with the existing back end more effectively.

                  I can help you design a replacement, but trying to work better with a backend that you can't even describe at this point seems pretty much impossible for me to suggest much. I can only observe that your back end might very easily have separate address and contact tables for all that I can tell from here.

                  • 6. Re: Stuck on relationship
                    MarcMcCall

                    No, I am just trying to set up accounts on the webstore, with the account that are in our back office sytem.  I am trying to mirror it as best I can.  The back office system has a built in website capabilities, but stink.  This new one is great, with more fucnctions like order approval process for large companies, line item level cost center setup, and department setup for customer accounting purposes.  Multiple catolog, and built in marketing tools, contract pricing, customer budgets... ect.......

                     

                    Anyway, no I am just trying to set up all of the accounts on the webstore.  I am in charge of setting up accounts, and maintaining it once we go live, in addition to building the company owned product catalog.  It would be nice if they were both combined into one DB but it's not, there are 2..  I'm just trying to make my job easier setting up the web accounts.