Use the already existing portal and sort the portal relationship on Quantity and you will have the article with the highest Quantity first in your portal.
I more beautiful way is to create a report layout where you sort Sub Summary Part 1 with your Customer, Sub Summary Part 2 with your Article and in that part have a Summary field of Quantity. Layout should be based on your Order Item table. Do a script that goes to that layout, sort on Customer and then Article and then Quantity and the result will show each Customers (A-Z) different Articles with highest Article first
There is no portal, there would be easy. I need to get it from a calculation, not a sub summary not a portal.
You got two options then
1. Build a relationship between the two tables and create a new calculated field in Customer with Sum(RelantionshipName::Quantity)
2. Use ExecuteSQL to get your data in a calculated field
Yes I did both,
Btw I'm confused on:
1. How to get the id of the article that has the biggest sum(qty) (I need the article id, not the qty)
2. The structure of the sql query.
I already know how to create a sql thanks
Then use ExecuteSQL in your field to find the Article with the highest Quantity and get Article ID back as the search result
how to get the articleid on that sql query?
from orders o
on c.customerid = o.order_id
order by sum(o.qty)
Do you think it would work?
How to do it with a relationship graph?
If use decide to use ExecuteSQL I need all your table and field names to be able to write you a SQL query. But since you know how to write SQL, I guess you won't need that answer. From what I can see in your SQL query you are up to a good start.
No need for any relationships since you can use Group in a SQL query to get the total of a specific Article.
But relationship on sql aren't useless?
(i have to correct the join rel. based on
c.customerid = o.customerid)
Just to give you a completed view of the tables and fields
Articles pk = __pk_id_article
Customer fk = _fk_id_article
Order fk (to customers) = _fk_id_customer
Customer pk = _pk_id_customer
Quantity (for each article on each order) = qty
Thanks in any case!
1 of 1 people found this helpful
Using ExecuteSQL() - the function - and creating the query that FileMaker can use, can be very different than a query that MS SQL, Oracle or MySQL can use.
I know how to create a SQL for all 4 of the above databases. They are different, in some ways. Using the mentioned 'tool' from SeedCode (a FileMaker database to create the queries) can be helpful, so you shouldn't just dismiss it.
I'll have a look at it then!