Jump to content

Welcome the ORIGINAL FileMaker Community

Take a moment to join us, no noise, all FileMaker...We Promise

jrinear

How can I filter results through Relationship Graph, not Portal Filter

Recommended Posts

jrinear

I do a lot of business with schools and created my own CRM to track sales and points of contact (phone calls, meetings at trade shows, etc.), as well as give me insights into trends.

In my sales/invoices tab I have for each person I have a few different portals that show me different sales relationships. One portal I have shows me all of the sales within the school district, related to a person. For instance, a person may teach at a particular school and that school may be one of 20 schools within a district. The portal shows me other teachers that have purchased within the same school district. The idea is to find/develop potential customers with the idea that, if one person from a district has purchased I can utilize them to champion our products and build upon those teacher-to-teacher relationships to generate more sales.

So… I have the portal working except for one thing I just cannot figure out how to do. I need to see all of the invoices within the school district WITHOUT including sales from the person where the portal lies – I need everyone else’s sales within the district. This can easily be done with a portal filter, but because I need to use the info for a calculation that utilizes that info (most recent and second most recent sales, as well as a number of other things using the same basic idea) I need to do this in the relationships graph.

The table relationships are set up essentially move from Name to School to District, back to School back to Name, from Invoice to a third T.O. of Name. Here is precisely how the tables/relationships are set up, forward joins shown:

NAME T.O.#1 (I called the table  ‘Name’, not Person); pkNAME joined forward to fkNAME in NAME-SCHOOL-JOIN T.O.#1:

NAME-SCHOOL-JOIN T.O.#1; fkNAME joined forward to pkSCHOOL in SCHOOL T.O. #1:

SCHOOL T.O. #1; pkSCHOOL joined forward to fkSCHOOL in SCHOOL-DISTRICT-JOIN T.O. #1:

SCHOOL-DISTRICT-JOIN T.O. #1; fkDISTRICT joined forward to pkDISTRICT in DISTRICT:

DISTRICT; pkDISTRICT joined forward to fkDISTRICT in SCHOOL-DISTRICT-JOIN T.O. #2:

SCHOOL-DISTRICT-JOIN T.O. #2;  fkDISTRICT joined forward to pkSCHOOL in SCHOOL T.O. #2 :

SCHOOL T.O. #2;  pkSCHOOL joined forward to fkSCHOOL in NAME-SCHOOL-JOIN T.O.#2:

NAME-SCHOOL-JOIN T.O.#2; fkNAME joined forward to pkNAME  in NAME T.O.#2:

NAME T.O.#2; pkNAME joined forward to fkNAME in INVOICE;

INVOICE; fkNAME joined to pkNAME in NAME T.O. #3;

NAME T.O. #3

The PORTAL that shows MOST of what I want is in the table NAME T.O. #1 and is related to the table INVOICE, most of the fields being shown in the portal are from INVOICE except for the actual name field of the customer, NAME T.O. #3. It shows everyone invoice from the district along with the name of the purchaser. I simply want to filter out the NAME of the record I am on in this portal, leaving only the other purchasers in the district.

Not sure if I explained too much, too little, not clearly enough, etc. I can attempt to make any clarifications needed. Any help would be greatly appreciated!

 

 

Share this post


Link to post
Share on other sites
Josh Ormond

Have you tried using the ≠ relationship?  Use the person's unique ID as part of the relationship, but change it from = to ≠ instead.

Share this post


Link to post
Share on other sites
jrinear

Thanks so much for your input.

I have tried that and was surprised I could not get it to work actually. As you can see, I have three different T.O.'s of the NAME table. I tried it in each of those, and I also experimented with changing the portal relationship as well. In most of the permutations I  got all of the invoices (I have just begun to export invoices from Quickbooks, so I have only a manageable few months worth at this point), not just the ones related to the school district, and STILL got the invoices of the person whose record it was (ie in John Smith's record I still got John Smith's invoices, which is exactly what I am trying to avoid).  Again, I really thought the ≠ would work, so I have wondered if I just haven't stumbled across the correct portal relationship and correct fields in those portals. For instance, I have three choices of which customer name field I could choose - the one from NAME T.O. #1, NAME T.O. #2, or NAME T.O. #3.

Share this post


Link to post
Share on other sites
Steve Martino

Pictures would help, as would a copy of a database with enough sample info.

Seems like you just need another TO to create the portal of records you wish to view.  Hard to tell by your worded description but maybe from Invoices to Schools?

  • Like 1

Share this post


Link to post
Share on other sites
jrinear

Thanks Josh and Steve. I did try some other T.O.'s but could not quite seem to get them right. I will upload picture of my relationships graph in the next day or so. I just need to drag things around so you can get a clear picture of the relationships/relationships path I am referring to. Regarding a copy of the file, I'm not quite sure of the forum protocol. I would certainly be willing to send either Steve and/or Josh a copy of my solution, unedited. If I were to post online I would have to delete much of my pertinent records and I am a little concerned I would inadvertently miss something that was sensitive, as there are many related tables. Suggestions as to how to proceed? And again, thanks for your input!

Share this post


Link to post
Share on other sites
Josh Ormond

I sent private message to you. I'll post back after I've had a change to review the file.

Share this post


Link to post
Share on other sites
jrinear

Here is a screen shot of the relationship path. I moved everything else out of the picture so you could clearly see the path. Not sure if I am using the best naming convention, but so far it has worked for me. The different colors are all different T.O.'s of the same table except for grays- all the greens are different T.O.'s of the NAME table, all the grays are JOINS, but the JOINS are from two different tables. The capitalized letters in the name all show which table they are from. I used underscores to show the relationship path, until the INVOICES table - got too cumbersome to follow the whole relationship path in this manner. But for most relationships this naming convention has been helpful for me.

I am guessing you may still need clarification after seeing this, so please let me know if you do.

139131735_FilemakerCRMScreenShot.png.9e95ddd1185dbcfe11dff4fd2ea313e1.png

Share this post


Link to post
Share on other sites
Josh Ormond

That is a lot of jumps across the graph. Can you list out in simple plain words the purpose of was having jump and what you intend it to do?

Next, I would try to consolidate some. Can you move some of the designations into a single join. Also for the invoice, can you place foreign keys for each the join attributes in the invoice itself? For example, fk for the person, district, school, etc. Then you could reduce the portal to a single hop. 

Share this post


Link to post
Share on other sites
jrinear

Being largely self-taught on FM Pro (except for YouTube tutorials and Lynda.com) I am aware that there are probably a great many things that are not 'best practices.' Over the years I found some things that seemed to work and went with those.

Let me explain the first half of the relationship graph, up until school district. Because each NAME (person) is a member of a school and each school belongs to a district, there is no direct Name-to-District relationship, it must be linked by a school. What I found, and this is where I may have gone wrong (but maybe not) is to be able to get everyone else to show up in the portal from related schools in the same district I had to link a new T.O. of SCHOOL to the DISTRICT. In order to actually show those names in the portal I had to link that new SCHOOL T.O. to a new T.O. of NAMES because the SCHOOL table has no actual names in it. 

The way I have invoices set up is that each invoice has two foreign keys - it is associated to a SCHOOL and a NAME. This is because teachers often move around from school to school - and this also makes the CRM handy - if I know a particular school purchased particular products and a new teacher moves in, it is useful info to market to that new teacher. Vice-versa is equally useful. If a teacher used our products and moves to that new school it is very helpful to be able to track that. So regarding your comment about multiple foreign keys in the invoice, I am not sure if this kind of thing is what you are referring to. As always, your help is appreciated!

Edited by jrinear

Share this post


Link to post
Share on other sites
Josh Ormond

So, to make sure I have the data correct in my head.

Looking at the Joshua A. record, in that portal you would expect to see the following invoices with the primary keys:

  • 33, $1564.20
  • 36, $1711.40
  • 47, $69.96
  • 66, $1259.29
  • 48, $1568.12

Is that correct?

If yes, there are 2 fairly easy options, other than jumping a long trail of TOs.

  • add a School District field to the invoices. This can be scripted to fill in the data after you create the field.
  • build the list of invoices on the fly, when you go visit the record.

Which one really depends on what you are using the list for. If the purpose is just a visual list for you to look, I would go with the later option. If you need that data for a report, or some other action ( like sending an email, compiling a list of recommended contacts, etc ), then I would go with the former option.

Share this post


Link to post
Share on other sites
jrinear

That is correct. The invoice for $434 with a primary key of 65 should be omitted.

The data would not just be for a visual list, but for a calculation. Essentially what I am doing is creating a 'lead rating system', where I automatically calculate rating numbers for what I feel are attributes that contribute to a strong lead. One of these attributes would be different people from the same school or district have purchased before. Then I would have one or a few rating numbers display on the pseudo dashboard I have at the top of each record, and I can also run searches for certain ratings, all automated. Because of this, I can't just use as portal filter - if I use something like an Nth record function in my calculation it will ignore the portal filter (at least it did in my tests) and sometimes get results I do not want.

So... - a couple of things. If I add a district field to my invoices would this be adding information that is already in the CRM? If the information already exists would it be better to just filter it out rather than add another field with new info? But perhaps adding another field is the best way to go. Also, I would still need to get same school but different person purchases. Would the new field in the invoice accommodate that or would it filter those out?

Thanks much!

Share this post


Link to post
Share on other sites
Josh Ormond

Were you able to work through this? If not, I can send you a file tonight that shows a way to do it that may be easier. Sorry, things have been insane this week at work.

I have a working version of this, after a couple field additions and a new relationship.

Share this post


Link to post
Share on other sites
jrinear

I have not been able to work through it yet. I think you have my email, so if you send the file when you get a chance that would be great. No need to apologize for the timeliness of the advice - I'll take it whenever it comes in!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Terms of Use