Multiple Many to Many Relationships

[Davesmind]

I need a little help getting my relationship filtering to work. I've searched through many of the examples and issues others have had, but for some reason i did not find an answer to my problem. I've tried join tables and multipe one to many tables and while I can get some of the results i'm looking for, i know my end result is incorrect and there is a better approach to the data.

 

To start with; I am given a table of transactions that contact a salesperson and customer (around 200,000 records).

 

My data table will have a record looking like this:

 

salesperson_id ; customer_id ; sales amount ; date

Bob1 ; Widget100 ; 100.00 ; 1/2/2015

Bob2 ; Widget100 ; 100.00 ; 1/2/2015

Carl1; Widget200; 100.00 ; 1/2/2015

 

I need to break this table down according to the salesperson and their assigned customers. The issue is each salesperson can have multiple salesID's. I do not believe that the different salespersons can have the same customer, but due to the multiple salesperson id's i guess it is almost the same. Unfortunately since I am inheriting bad data, an older system generates these records, I am finding not only an issue with tying the multiple salesIDs to a single person, but the sales id can be similar as in BobE and BobEl. I did find that BobE can see BobEl data but not vice versa.

 

I am looking to build a sales tracking viewer. In this i want to create a layout with the salesperson displayed. I want to be able to add all of the salesID's this person can have and all of the assigned customers. When I add a customer I want to then total all of the sales for that customer. 

 

SalesPerson (pk_id, salesperson)

                      ---->   salesperson_codes (fk_id, sales_id)

                      ---->   customer_codes (fk_id; customer_id)

data_table ( salesperson_id; customer_id; amount)

 

I need to be able to create a sales person, assign multiple salesperson ID's to their account, then add in the customer's assigned to them. I figure I can add both the salesperson_codes and customer_codes as portals on the salesperson layout. I want to assign the salesperson_id's in the one portal. The second portal would have the customer_codes and then summary fields that will total the sales for that customer. So far I've been able to get one side working, but not both. Any help would be appreciated.

[mikedr]

Would it be possible to have three tables: salesperson, salespersonID, and customerID?  You would have a one-to-many relationship between salesperson and salepersonID, and a one-to-many relationship between salespersonID and customerID. 

 

You should then be able to set up two portals on the salesperson layout: one listing all the salespersonIDs for that saleperson, and another listing all customerIDs for that salesperson.  The latter table would be related to the salesperson table through the salespersonID table.

 

I kinda do something similar.  I have a patentapplication table, a correspondence table, and a correspondence type table.  The app-corresp relationship is one to many, but but corresp-corresptype table is one to one.  I set up a portal that shows correspondence for each application, but some of the information shown in the portal is grabbed from the corresp type table.

[comment]

This part doesn't make quite sense:

 

The second portal would have the customer_codes and then summary fields that will total the sales for that customer. So far I've been able to get one side working, but not both. Any help would be appreciated.

 

Do you want to total the sales by customer in the salesperson's layout? IOW, see the summaries for one salesperson at a time?

 

If not, then forget the second portal and create a report from your "data" table. Summarize the data by a calculation field returning the "real" (unique) SalesPersonID, which you can get through a relationship chain:

 

Data -- [salesperson_id] --< salesperson_codes >--- [SalesPersonID] -- SalesPerson

 

 

Note:

You could also enter the salesperson codes as a return-separated list in the SalesPerson table itself and eliminate the salesperson_codes table.

[Davesmind]

Thank you for responding. I have been working on the issue. Please refer to my attachment for the structure of what I have done so far. We have a set list of customers that each sales person is assigned. Because of this I setup relationship to filter out by the customer ID and the specific fiscal year. The fiscal year also contains a target forecast for each customer. On that end it goes data->salesplan->customer.

Outside of this I also need to know the totals for the entire year. We then take that total and subtract the totals for the customer list. This will give us the customers that they do not track. The below table is exactly what they are looking for.

 

Salesperson Name

 

Customer                              Last Years Sales                Current Year Sales             Forecast Target                   To Target

CustNo.1                                      $500,000                                  $100,000                       $550,000                          $450,000

CustNo.2                                      $350,000                                  $50,000                         $375,000                          $325,000

 

Then a separate line on the same screen:

 

Other Customers                        $100,000                                   $10,000                         $110,000                         $100,000

 

In this area, in order to get the total, we need to now create a relationship that tracks the 2 id's most salespersons have. I use a single field and just added each unique identifier as a line (salesperson_codes -> fk_sales_codes). It isn't optimal, but it works. The fiscal year table is where we chart the remaining customers Forecast Target. 

 

This report has been a little frustrating as each time I think I have the solution, I find another variable that creates even more complexity. Either way when this is done it should save our sales staff hours upon hours of time. Win/Win for Filemaker! Thanks for any help!

[comment]

I am afraid you've lost me somewhere along the way. To take a simpler case first, consider the following structure:

 

SalesPeople -< Customers -< Sales

 

Now, if you go into the Sales table and find the sales for the last and current year, and sort them by SalesPeople::SalesPersonID first, then by CustomerID and then by Year (calculated from the date field in Sales), you will be able to produce a report looking like this:

Salesperson: Adam
• Customer: Alpha
   • Year 2014: $500,000
   • Year 2015: $100,000                      
• Customer: Bravo
   • Year 2014: $350,000
   • Year 2015:  $50,000 
  
Salesperson: Betty
• Customer: Charlie
   • Year 2014: $280,000
   • Year 2015:  $80,000

very simply, just by using sub-summary parts (three in this example, one for each field in the sort order) in your report layout.

[Davesmind]

I know it is confusing. Our company has a culture of Excel spreadsheets. Many of my reports need to be from left to right and include data from a past year for comparison. I have managed to create a work-around for many issues. In most cases, because the data is usually static, I can even use just SQL to pull the data into the fields needed. I was really looking for a solid FM relationship, but because I am working backwards with the data and they want to see multiple criteria on the same screen, it seems to get overly complex. The sub-summary will not work since I need the data on the same line for the year to year. The second issue is that while it is easy to just pull the top 25 customers and list them, I then need to find out what the yearly total was, remove the Top 25 from that total and then provide a that number so that a plan and target can be created on the catchall alone. 

 

In my schema I am using the Customer_ID table as my viewing table. The sales_plan -> super_stats is where I pull the customer and the yearly totals. The Customer_ID -> Sales_team -> Fiscal_Year is where I pull the sales person. Normally this would be simple, but each sales person can have multiple ID's (poor DB construction of the past). I resolved this by pulling with a field that has each ID followed by a carriage return. 

 

In the end, in order to view the data on the Customer_ID layout i created a Global field to trigger the year, so I can switch between years, or even show past years. I then need to pull all of this into a single report this is where the biggest issues will arise. The report will consist of a line below:

 

Salesperson (Sub Summary)

 

Body (List layout) 

Customer: Alpha  |  Forecast for Current Year [2015] (pulled from sales_plan | Sales to current plan current sales - plan | current sales [2015] | Last Year sales [2014]

 

That is a simplified version. I will need to perform that line for each customer and then add in the same for the catchall. I will also have to add a breakdown of booking, billings, GP%, GP$ and so forth comparing to plan, current and last years totals. I will probably run a bunch of SQL queries to pull the data. I was looking for a more solid FM approach, but in the end SQL may be the only way to approach this without generating a huge headache for myself. 

 

Thanks for your help. I love these kinds of problems as it forces me to research and develop my FM skills beyond the normal setups.