Method Community

Hi. I have a question that I'm not sure how to ask or categorize so I'm just going to tell you the scenario and ask if we can get to where we want.

Currently we have a "Call Report" screen with a Call Report #.  We also have a "Field Service Report" screen with a Field Service #.  This means we have 1 Call per issue and 1 FSR per Call so they can be related and searched.  This has worked so far.

Now we would like to have multiple Field Service Visits per Call. i.e. Customer A has Issue 1 and we went to the site 3 times to fix it. Right now we have 3 Calls and 3 FSRs to document this.  I'd like to have 1 Call with a referrence to all 3 FS visits.

Conversely, I'd also like to have 1 FS visit solve multiple Calls.  i.e. We went to Customer A one time and fixed Call 1,4, & 7, all in the same visit.  So, we'd need the Field Service Report to referrence all 3 Calls that were fixed.

I have a Call # and FSR # FIELD on both forms to reference eachother.  They are both in the GRID and can be searched and sorted by either from either screen.

I know I can just add additional FIELDS with referrences to more call #s or FSR #s but how do I search or sort it.  I'd rather not have 3 more FIELDS added to the GRID so when I'm searching for a Call from the FSR screen I'd have to look in 3 different columns for it.

I'm looking for something like OUTLOOK where I can search for "Sue" and it will find every email that has "Sue" in the email address or subject or body of the message.  I'm not sure if that is possible with Method but I figured I'd ask.

Thanks,

Greg at WaveTec

Wavetec:

Now we would like to have multiple Field Service Visits per Call. i.e. Customer A has Issue 1 and we went to the site 3 times to fix it. Right now we have 3 Calls and 3 FSRs to document this.  I'd like to have 1 Call with a referrence to all 3 FS visits.

In this scenario I could see one table being used as a lookup table.  It should have a field to store the record ID's from your "Call Report" table and the Entity.  With that setup you should be able to pull in enough information to display a list of Customers grouped by the Call's associated with them.

Wavetec:

I'd also like to have 1 FS visit solve multiple Calls.

With your lookup table you could add a dropdown field (outside the grid) for FSRs.  Then you could program a Loop action to update checked rows (or Calls) from the grid with the FSR selected in the drop down.  The setup might be a little complex to understand but you would have greater control over job assignment on one screen.  You could also build some nice reports from a table like this.  Sky's the limit here.

Wavetec:

I know I can just add additional FIELDS with referrences to more call #s or FSR #s but how do I search or sort it.

You can search on up to 3 fields with the advanced properties in each grid columns.  Combine that with filters and you can build some very complicated search/fliters here.

Wavetec:

I'd rather not have 3 more FIELDS added to the GRID so when I'm searching for a Call from the FSR screen I'd have to look in 3 different columns for it.

Additional fields to the table with duplicate information is a very bad idea.  They can really slow things down over time.  For example, say you have a need to store more than one email for each customer.  You should not go into the Contacts table and add Email1, Email2, Email3, etc.  This would slow that table down and not allow for future expansion easily.  Good database design would have you create a CustomerEmail table and store 2 pieces of info, Entity and Email.  This will allow you to add an endless number of emails for a customer and not have a speed impact when you go looking through the Customer table.

Wavetec:

I'm looking for something like OUTLOOK where I can search for "Sue" and it will find every email that has "Sue" in the email address or subject or body of the message.

You can add additional columns to search in the advanced properties within each grid column but you're limited to fields in the same table.  I.e. In the Customer column on a grid I can add 2 more fields to search by but only fields within that same table.  I could not add the customer column and then have it also search the body of an email from another table.

Hope this helps.  If you get stuck or need some help building something like this please feel free to reach out for a consulting session.

~C

Method_Chad:

With your lookup table you could add a dropdown field (outside the grid) for FSRs.  Then you could program a Loop action to update checked rows (or Calls) from the grid with the FSR selected in the drop down.  The setup might be a little complex to understand but you would have greater control over job assignment on one screen.  You could also build some nice reports from a table like this.  Sky's the limit here.

Thanks for the info Chad, but I'm not getting it to work so I think I'm missing something.  I tried to add a GRID to the Call Report screen that referrenced the FSR # & Event description, but I can't seem to get it to work right.  I thought I could have a LIST on the page that I could build for all the FSR's related to that call.  I'd have a pull down or something to enter the FSR# and it would pull over the event description fromt he FSR table.  but all I get are 2 blank areas and entering a # doesn't do anything even though I have "TEXT CHANGE" looking up the info and I'd really love a drop down so I don't have to enter the number "exactly" as it is in the report.

Do I need to build another table first and then point the GRID at that?  I built a PARTS LIST table that I have a GRID referrence in another screen and that seems to work but that is just a straight forward table of Part#s and descriptions.  This is referencing another screen, which is built from a table but isn't strictly a table in the normal sense. 

thanks in advance for your assistance.

Greg

HI Greg,

I read your original post and it got me thinking about how I might solve this problem, thought I'd share. Much of this overlaps with Chad's comments:

- What you're describing is a many-to-many relationship, not a one-to-many. As you said, one FSR can have many calls, but one call can also have many FSRs. 

- The way to properly handle a many-to-many relationship is to have a third cross-reference table which has the unique record keys from each of your two tables. 

- Just in case this wasn't clear (it wasn't for me!), a GRID and a TABLE are two different things. A grid is a way to display data from a table on your screen. A table is where data is actually stored, as records with unique RecordIDs. 

- Another Method fact: every table comes with a built in "RecordID" field, that acts as the unique key for that table. We like to add fields like "FSR #", "Call #", "Invoice #",  etc, which are also supposed to be unique values; but unlike RecordIDs those fields are of your own creation, meaning you can change their values, opening the possibility of accidentally  having two FSR's with the same #. RecordID values are unchangeable, adding a layer of security for database management. 

Call your third "cross-reference" table whatever you want .There are only three fields that are vital for it to contain:

1) The table's RecordID (which comes prebuilt in the table, so you don't need to add it as a field),

2) the "FSR#" (or, better, the "FSRRecordID") field,

3) and the Call# (or, better, the "CallRecordID") field.

You can add more relevant fields if you like - the customer, for example. But since that data is probably already in your FSR or call table already, it's not necessary. Also, I believe you will be benefited further on if you add the FSR# and Call# as Linked Fields, meaning they will reference the values in the FSR and Call tables, instead of you having to copy those values over. 

The tables records will look like this:

Record 1  |  FSR # 1  |  Call A  |  Customer X

Record 2  |  FSR # 1  |  Call B  |  Customer X

Record 3  |  FSR # 2  |  Call C  |  Customer Y

Record 4  |  FSR # 3  |  Call C  |  Customer Y

In these example records, you can see that Customer X made two calls that were handled with one FSR, while Customer Y had two FSRs that were in response to one Call. 

So now you have this table, but how do you relate multiple calls to an FSR and vice versa? You've got options for how you handle it, like a dropdown right in the FSR and Call screens, or just a button that opens a popup screen based on the cross-reference table, or some combination of both. Here's a quick idea on how to do it

1) On your FSR screen, have a dropdown based off the Call# table, filtered by customer.

2) Add a button (either the dropdown button or a separate one if you want a nice label for it), whose action set inserts a new record into the cross-reference table which inserts the screens FSR# (or Record ID), and the Call# that you select from the dropdown.

3) Now you can stick a grid in your FSR screen based on the cross-reference table (you'll only be able to base it on this table if it is linked to your FSR table, hence my suggestion for using linked fields earlier). Have the grid filter to only show records that involve the FSR # that you have open on the screen, and for columns you'd really only need to add a "Call #" column (also maybe call date, or other call table data that you copy/link over to your cross-reference table). 

4) Do pretty much the exact same thing for your Call screen.

With this setup, you should be able to link an FSR to a Call and vice versa mulitple times, using that dropdown. The grid you inserted will show the calls that you linked to the FSR right there on the screen.

There's plenty of customization I didn't mention that would clean this up. You can add a screen to allow editing of those relationships (eg deleting an FSR / Call relationship that you accidentally created). You can add useful features, like having those Call #'s on the grid in your FSR screen be selectable, so when you click them they'll take you straight to the call screen. There are probably additional filters, validation checks, etc that would be useful in this setup, but that will become clear with trial and error. 

Hopefully you can get through this without going crosseyed. If any part wasn't clear let me know, I'd be happy to clarify. But if it made sense and you want to give it a shot, I'm also happy to offer specific advice on action sets related to this. Compared to the stuff I'm trying to do on my own account, this is downright pleasant!

@method-chad

I wanted to jump in on this thread, because I have a very similar scenario that I am having trouble representing in Method. 

I am a building consultant.  I get involved in building projects, which we track as "opportunities"  

While working on project, I communicate with multiple trades who we do not sell or buy from (contacts) not vendors or customers

The first contact, in this example; is a plumbing contractor (1).  His foreman on the site is a sub-contact(1b), related through the plumbing company.  

Another contact is an electrician.  (2)

Another contact is the building owner(3), he has a sub contact of "project manager" (3b)

Another contact is the project Architect.  (4)  He has two junior associates (4a & 4b) 

Not too difficult, right...

Heres the twist.  

Add another project  P2 

Same Owner (P2-3)  different project manager (P2-3b)

Same Architect (P2-4) one different associate (P2-4a & 4c)

Different Election  (P2-2alt)

Same Plumber and Foreman.....

Thats the basic schema.  Multiply by 20 with infinate variations & you have an idea of the complexity. 

What I want CRM to do is: Poll the database for Opportunity or project & return associated contacts and companies. 

Poll the database for sample company (electrician) and return associated opportunities. 

Ideally, I would be able to poll database for opportunity records....and return group emails or case emails associated with project. 

Do you understand the question? 

Do you have advice for implementing process? 

KieranD:

Do you have advice for implementing process? 

My advice is similar to Smoyhee's above.  It sounds like you need to build a project contact relationship database.  The table would look something like this...

Project Contacts

RecordID |  ContactID |  OpportunityID    
Record 1  |  RecordID from Contacts |  RecordID from Opportunity  
Record 2  |  RecordID from Contacts |  RecordID from Opportunity  
Record 3  |  RecordID from Contacts |  RecordID from Opportunity