1 2 Previous Next 16 Replies Latest reply on May 19, 2016 10:47 PM by andresen

# How many orders so far this year / customer

Hi

I got answer from my earlier question very fast. Thanks. Link: https://community.filemaker.com/message/568681#568681

I think some similarity is possible when I also want to find how many orders/customer so far this year.

I have Order table and customer table and in customer table I want to see the result in a calculation field.

Help?

• ###### 1. Re: How many orders so far this year / customer

It's not just similar, it is identical in structure – except that you specify a different date range / start date:

ExecuteSQL ( "

SELECT COUNT ( \"orderID\" )

FROM \"Order\"

WHERE

\"customerForeignKey\" = ? AND

\"orderDate\" >= ?

" ; "" ; "" ; Customer:primaryKey ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) )

)

As before, make this unstored – and note that all these unstored calculations can be a real drag on your database's performance.

• ###### 2. Re: How many orders so far this year / customer

ExecuteSQL ("

SELECT COUNT(\"__OrderIDpk\")

FROM \"Order\"

WHERE \"Datum skapad order\">=?

AND \"Kund\">=?";

"";

"";

Kunder::Kund;

Date ( 1 ; 1 ; Year ( Get ( AktuelltDatum ) ) )

)

Now I get only the ? as a result

Can it be something whit the WHERE and AND ?

• ###### 3. Re: How many orders so far this year / customer

You are passing the arguments in the wrong order, i.e. the wrong argument goes to the right placeholder (or vice versa); it must be

ExecuteSQL ( "

SELECT COUNT ( \"__OrderIDpk\" )

FROM \"Order\"

WHERE

\"Datum skapad order\" >= ? AND

\"Kund\" = ?

" ; "" ; "" ;

Date ( 1 ; 1 ; Year ( Get ( AktuelltDatum ) ) ) ;

Kunder::Kund

)

EDIT: I totally overlooked that you are using the wrong operator for the customer: that should only be = (equal to).

• ###### 4. Re: How many orders so far this year / customer

change the date range to whatever is "so far this year".

GetAsDate("1/1/" & Year(Get(CurrentDate))) // start date "this" year

GetAsDate("12/31/" & Year(Get(CurrentDate))) // end date this year

These would work whenever a year changes

beverly

• ###### 5. Re: How many orders so far this year / customer

ExecuteSQL("

SELECT COUNT(\"__OrderIDpk\")

FROM \"Order\"

WHERE \"Datum skapad order\">=?

AND \"Kund\"=?";

"";

"";

GetAsDate ( "1 / 1 /" & Year ( Get ( AktuelltDatum ) ) );

GetAsDate ( "12 / 31 /" & Year ( Get ( AktuelltDatum ) ) );

Kunder::Kund

)

This did not work as planed. Only get the ? as an answer.

• ###### 6. Re: How many orders so far this year / customer

There you go:

ExecuteSQL ("

SELECT COUNT(\"__OrderIDpk\")

FROM \"Order\"

WHERE YEAR (\"Datum skapat order\") = YEAR(CURDATE)

AND \"yourCustomerIDFieldHere\" = ?";

"";

"";

customer::yourCustomerIDFieldHere

)

and into Swedish.......

ExecuteSQL ("

SELECT COUNT(\"__OrderIDpk\")

FROM \"Order\"

WHERE YEAR (\"Datum skapat order\") = YEAR(CURDATE)

AND \"kund\" = ?";

"";

"";

kunder::kund

)

• ###### 7. Re: How many orders so far this year / customer

You have TWO "?", but THREE parameters.

See Damian's answer for pure SQL functions

YEAR (\"Datum skapat order\") = YEAR(CURDATE)

beverly

• ###### 8. Re: How many orders so far this year / customer

beverly Seriously love the idea of being referred to as pureSQL

• ###### 9. Re: How many orders so far this year / customer

With regard to dates into and out of SQL. Don't move dates from FileMaker to SQL and back again unless you have to. If you do knock up some custom functions otherwise its always a hassle. SQL has plenty of date functions to do anything you might do in a FileMaker calc.

While I am on the good ideas, creating custom functions to quote text, validate numbers and do other useful things is a good idea too.

• ###### 10. Re: How many orders so far this year / customer

I still only get the ? Why?

ExecuteSQL("

SELECT COUNT(\"__OrderIDpk\")

FROM \"Order\"

WHERE YEAR(\"Datum skapad order\")= YEAR (CURDATE)

AND \"Kund\"=?";

"";

"";

Kunder::Kund

)

• ###### 11. Re: How many orders so far this year / customer

one thing that may help OP (at least get started) is this utility (FileMaker file) for helping write the queries:

beverly

1 of 1 people found this helpful
• ###### 12. Re: How many orders so far this year / customer

ExecuteSQL("

SELECT COUNT(\"__OrderIDpk\")

FROM \"Order\"

WHERE YEAR(\"Datum skapad order\")= YEAR (CURDATE)";

"";

""

)

What does that get you?

• ###### 13. Re: How many orders so far this year / customer

Same result.

• ###### 14. Re: How many orders so far this year / customer

is Datum skapad order a date field?

try

ExecuteSQL("

SELECT COUNT(\"__OrderIDpk\")

FROM \"Order\"

WHERE \"Kund\" =?";

"";

"";

Kunder::Kund

)

See how the logic in trying to find the fault?

1 2 Previous Next