Method Community

Hi there,

      How can I create relatzional tables in Method? I have a table counties (county subscription for our service is a product we sell) I want to relate this table to leads table with relation one to many (one lead could be interested in more then one county). I also need to make a combined field for county/state, sonce that combination is unique (counties with same name exist in different countries).  I've noticed those in leads grid but I'm not sure how to create them myself.

Hi Nebo,

First off, what is the end goal here for these relationships?  There may be a better route to take depending on what you are trying to achieve.

Assuming you need a structure like this, I'd recommend creating another table to house both the customer and the county fields as dropdowns.  The county dropdown would be connected to your County table, the Customer to the Customer table.  You can then add a grid for this table as an attached grid, similar to how our InvoiceLine grid is setup in the Invoice screen.

Then all you need to do is either add the county field to the screen and a button to create a record of the county and customer relationship to the grid. Or have it function like our InvoiceLive grid and to add a line and use the dropdown to select the county.  You could also add your State field to the table to create that relationship as well.

I recommend creating a copy of our invoice screen and taking a look at the screen for an example of how this would work. Feel free to post any follow up questions.

- Adam

Hi Adam,

    Here is what I'm trying to do:

County subscription for phone leads is our main product. I already have list of counties imported on a custom screen. Those counties could be used in 3 places:

1. Lead could be interested in counties (more then one)

2. Customers can own counties

3. We also have backorder list (list of counties that are sold but someone is interested in them)

What I want to do is merge fields County name and State alpha and make that merged field a key for the table. Then I want to relate it to those 3 tables. Also I want to make a columns in Counties table to identify is the county owned, is the lead interested or is there a backorder list and create views based on those.

What confuses me is how to make one to many relations and how to show that on screen.

Hi,

I think I have a grasp of what you are trying to do.  I just have a few follow up questions first. Am I correct in assuming that a customer can own multiple counties, but a county can only be owned by one customer?

Assuming that, I'm not 100% sure if I'm suggesting everything you need just yet, but here goes. First off, I think you need to create a table with your County,State field as a required and unique field. You can then add dropdown field connected to the Customer table (this will mark who owns the County,State). You can call it Owner. I'm assuming that since each County,State is unique, there would be no need for a County table. Just create the County,State table with all of the County State combinations (additional ones can always be added in). 

Then, just create a screen that will will add the owner of the County,State when its sold., putting the customer into the Owner field.

Then I'd create another table to house the County,State combinations that a Customer may be interested in.  You can create the table using a dropdown of the first new table (County,State) and a dropdown of the Customer table again. This time, add a linked field to the owner field from the County,State table.  This field will show who owns that particular county.  Since in this table, the County,State is not unique, the will be multiple entries of the County,State.

You can add this table to the Edit Customer or Edit Lead screen, much like I described already to show which Counties that Lead /customer is interested in.  If its already owned, you can create a filter on that grid for interested counties that are already owned, as a back order list.

- Adam

Here is how my table loks:

http://screencast.com/t/bvVk09R3nt6

What I need is how to create a field for combined County,State so I can use it as a key. Also I need to combine RecordID and company name since CompanyName is not unique as well, and it is a list too long to fix. It is easier to use recordID+CompanyName as a key, and then in dropdown.

What you'll need is to use a Character Function to join the two values.  That character function will save the joined values into an action result that you can then insert into the appropriate field in the grid. As long as you make that field unique in the table, it can act as your key.

- Adam

I have sucessfully merged the County with State from my counties list, using character functions as you suggested. Thanks for that one.

I have also done it with CompanyName and RecordID from Contacts table and put results into a third one called "CompanyWithID". When I export the table and search for duplicate entries in this column it gives no duplicates (I also checked for duplicates using PHPMySQL). But when I want to make this field unique in Contacts table (so I could use it in dropdown) it won't select the checkbox, even though it really is unique. What could be the cause of this?

Hi Nebo,

Where does the CompanyWithID field come from?  Is it its own table or just a field in the Contacts table?

If you are adding a new field to the Contacts table, you won't be able to make it unique.  This is becuase there are already records in the table and therefore by adding a new field, all values in that field will be blank and not unique.

What's the goal of this new field?  There may be another approach.

- Adam

      It is a field in Contacts table I've added. I'm aware that it is blank at start, but I've also run action set to populate it with CompanyName+RecordID. CompanyName is not unique column, I had to add it RecordID in order to relate Contacts with Counties. All the entries in CompanyWithID after running a script are unique, but I still can't mark the checkbox (unique) when I edit Contacts table.

      So I'm relating table Counties with Contacts trough separate table/screen called OwnerOfACounty, In order to add company name in dropdown I need it to be unique wich it's not. Rather then making it unique (it already has some 500 entries), I tried to make it unique by creating a new column and populating it with CompanyName+RecordID, since recordID is always unique this makes all entries in this column unique.

      I hope I've explained it properly.

Hi Nebo,

I think I've recreated the issue you are having. I'll need to investigate this a bit more to see if there's a limitation to this particular table or something else in play.  I'll report back here.

- Adam

Any update on this? Perhaps a workaround solution? I'm stuck too long on this and it kind of blocks my whole customization.

At first I whought that CompanyName is not unique because of duplicate entries we have created by importing invoices via API and converting Customers from leads. However if the Company have 2 contacts then it will have two entries there, so even cleaning out the duplicates wouldnt solve the problem. Usable solution would be that "CustomerwithID" but I can't select it. Comon, somebody must have been relating table contacts with a custom table in past. 

Also Customer table has a un ique field FullName and it is marked as a unique in method. However when I try to add it as a column for dropdown, there is only recordID.

Hi Nebo,

Sorry for the late reply.  I believe there might be something coded in the Contacts table to prevent adding a unique field.  The reason I say this is while this isn't a directly connected QuickBooks table, it will update and add the main contact for any new customer created in QuickBooks.  Since QuickBooks wouldn't know about the custom Method field, any new entry from QuickBooks would create a blank value for that field, and thus not be unique.  This would essentially prevent a user from adding a customer in QuickBooks and syncing it to Method.

There is a workaround you can employ, by using the RecordID field and altering the dropdown.  Let me dive into this and get back to you on it.

As for the Customer table and FullName field, can you try to regenerate the table?  You can do this by clicking Edit Fields for the Customer table, then clicking the button, Finish Editing Fields. Please let me know how that turns out.  If it doesn't work, a duplicate field may have gotten into the table.

- Adam

Method_Adam:

As for the Customer table and FullName field, can you try to regenerate the table?  You can do this by clicking Edit Fields for the Customer table, then clicking the button, Finish Editing Fields. Please let me know how that turns out.  If it doesn't work, a duplicate field may have gotten into the table.

Miracullously,  this worked. I'm positive I did this more then once yesterday, but yesterday it didn't work. Did you change something on backend side?

I'd like to hear about that workaround for Contacts table. I'll need it for another table.

Thanks Adam!

Hi Nebo,

Sorry for the late reply on this.  In order to load the Contact name, add another field to the table.  This will be a linked field from the Contact table using the Name field from the Contacts table.  Then, go into your Contact dropdown on the screen, edit it, and go to step 4 of 6. See the screen below:

Notice that you can use the Name field as the select field. This will show the Name associated with the recordID instead of the recordID itself.

Hope this helps,

Adam