mac | pc Help with Chart Many to Many relationship SQL

[obiejuankenobi]

Three tables in question are [CUSTOMERS], [uPDATE], [PRODUCTS]   the relationship is many customers to many products with [uPDATE] being the join table.   The only field in update is the primary key, the two foreign keys, and a date timestamp.

 

In the [PRODUCTS] table i have a (GROUP) field.

 

In the [CUSTOMERS] table let’s assume there are no fields that will matter in this except the customer ID

 

When i look at the Customer Detail Layout, Which is related to the [CUSTOMERS] table i have Products related in the many to many direction as mentioned above in a portal.

 

In this Customer Detail Layout, I want to show a graph chart that counts how many products the customer has based on the product (GROUP) Field.

 

Using fruit as an example,   let’s assume i have these products related to this customer from the [PRODUCTS] TABLE via the [uPDATE] join table.

 

[PRODUCTS TABLE] (NAME FIELD): "APPLE" (GROUP FIELD): "RED"

[PRODUCTS TABLE] (NAME FIELD): "APPLE" (GROUP FIELD): "GREEN"

[PRODUCTS TABLE] (NAME FIELD): "GRAPE" (GROUP FIELD): "RED"

 

What im trying to accomplish is a chart in the customers detail layout would show the following via graph.

RED: 2

GREEN: 1

[eos]

Translated to your nomenclature, you need this for X axis data …

ExecuteSQL ( "
  SELECT P.theGroup
  FROM Updates U
  LEFT JOIN Products P ON P.primaryKey=U.productForeignKey
  WHERE U.customerForeignKey = ?
  GROUP BY P.theGroup
  " ; "" ; "" ; Customers::primaryKey
  )

… and this for Y axis data:

ExecuteSQL ( "
  SELECT COUNT ( U.customerForeignKey ) // any non-empty, i.e. key field should do
  FROM Updates U
  LEFT JOIN Products P ON P.primaryKey=U.productForeignKey
  WHERE U.customerForeignKey = ?
  GROUP BY P.theGroup
  " ; "" ; "" ; Customers::primaryKey
  )

Make sure to select “Current Record (delimited data)” as the data source. Note that “group” is a reserved word in SQL.

[obiejuankenobi]

Should i put these calculated fields in the Update Table or Products Table?

Or does it not matter and make them global?

 

Thanks for the help!

[eos]

Should i put these calculated fields in the Update Table or Products Table?

 

Neither, because you want to chart by Customer – at least that is what this statement implies:

 

What im trying to accomplish is a chart in the customers detail layout would show the following via graph.

 

And this …

 

Or does it not matter and make them global?

 

… wouldn't work either, because you want to see the specific result for a customer – and a global field has the same value across the entire table.

 

So, if you want to see the chart on a customer layout, you need to filter the join table records by customer, and that is what this part of the SELECT statement is doing: *

 

  WHERE U.customerForeignKey = ?

  GROUP BY P.theGroup

  " ; "" ; "" ; Customers::primaryKey

 

This means the calculations only return correct results in the context of customers.

 

(* Yes, you already have a relationship between Customers and Updates that does the same thing, but you cannot use that in ExecuteSQL(). )

 

You could use the calculations to define (non-global) calculation fields in the Customers table, but then again, why not simply put them directly into the x and Y axis data definitions (using “Specify Calculation”) of the chart object?

 

Note that the same context rule applies: the chart object must be on the customer layout (or more precise: on a layout where Customers::primaryKey is accessible and returns correct results – which in the case of your database is probably an academic distinction).

 

Hope this all make sense, and that you can design a nice looking chart (with accurate numbers, of course!  ) 

[obiejuankenobi]

eos,

 

thanks so much for the help and detailed explanation.   Still having trouble getting it to work:

Here are my Primary Keys, did i do this correctly?

 

Customers: CUSTOMERID

Products: PRODUCTID

Updates: UPDATEID

 

They are placed directly in the chart as you recommended.,   Chart is in the customer detail layout with the customer id

ExecuteSQL ( "
SELECT P.theGroup
FROM Updates U
LEFT JOIN Products P ON P.UPDATEID=U.PRODUCTID
WHERE U.CUSTOMERID = ?
GROUP BY P.theGroup
" ; "" ; "" ; Customers::CUSTOMERID
) 

AND

ExecuteSQL ( "
SELECT COUNT ( U.CUSTOMERID ) // any non-empty, i.e. key field should do
FROM Updates U
LEFT JOIN Products P ON P.UPDATEID=U.PRODUCTID
WHERE U.CUSTOMERID = ?
GROUP BY P.theGroup
" ; "" ; "" ; Customers::CUSTOMERID
)

 

[obiejuankenobi]

Here is a link to the stripped down test database i am trying to work this on

https://www.dropbox.com/s/p1dfso8s35z50e2/Test.fmp12

[eos]

OK, after I've managed to adapt to your field names … 

 

And speaking of which: code suggested to you here will (hopefully) work in its logic; but when you copy it, you need to adapt it to your field and tables names, or vice versa (which was not the case). 

 

Also note that, if you put the calculations into calculation fields, you need to set the result type to text; what you want is not a number, but a list of numbers (which you cannot store as a number).

 

And lastly, SQL is non-forgiving if it encounters keywords and invalid words in the code (as noted before …); that's why a field name like “Group” must be escaped, or shouldn't be used in the first place. “theGroup” is OK.

 

Here's a link for more info about ExecuteSQL in FM. Have fun!

Test_eosMOD.fmp12.zip

[Lee Smith]

Here is a link to the stripped down test database i am trying to work this on

https://www.dropbox.com/s/p1dfso8s35z50e2/Test.fmp12

Please do NOT post your attachments anywhere else than here. Attachments posted in other places have a tendency of disappearing  once the OP has found an answer, thereby leaving a hole in the Thread here.

 

To attach a file to the FMForums, just follow the steps here.

[eos]

Attachments posted in other places have a tendency of disappearing 

 

How quite diplomatic of you to blame the attachments …

[obiejuankenobi]

Thanks very much for your help.  Great job!!