You've defined very clearly! Summary being the key here. The sum (per year per client) is needed. A Summary Field can give you the value, but you also need to apply that discount/refund. There is a function called
that might help, but must still be unique per client per year.
of related records might help, but you would still need to limit per year.
You might find the records in a given year and loop through them getting the sum and as a result set the value in another table related to the client
clientid, year, sumPerYear, discountToApply, etc.
ExecuteSQL() can be used at any time to narrow down by client by year. Perhaps settings fields as above, so you have historical data.
Sent from miPhone
I was going to try to help you with the ExecuteSQL, but wanted to verify the field names.
Do you really have a field "Period (year)" instead of "Period_year"? You may find, at some point (using ExecuteSQL for example), that the naming of fields (and other objects) in FileMaker is very important for usage elsewhere.
If the name I see in your post is not correct, please post the real name(s)?
p.s. I always have a field "yr_mon" which is an auto-enter text field that is alpha-sortable, searchable and handy for grouping (summarizing) for reports and charts:
Year ( myDate ) & Right ( "00" & Month ( myDate ) ; 2 )
I might add a "_" between the year and month to make it clear this is text and not a number, but the result is the same for usability.
I'm from Poland, so my field names are totally different I always use examples, and it was only part of my tables.
I'm a beginner with this Filemaker stuff, so I will be thankful for any help "step by step"
this is mostly an outline and you would calculate the final discount amount:
IN table customers, I might have a script to query (a loop) each customer
go to layout (customers)
show all records
go to record ( first )
set variable ( $year ; Year(Get(CurrentDate)) )
set variable ( $customerID ; CUSTOMERS::customerID )
go to layout (client_taxes)
set field ( client_taxes::customerID ; $customerID )
set field ( client_taxes::year ; $year )
set field ( sumPerYear ;
[ ~query = " SELECT SUM(amount) FROM OPERATIONS WHERE period_year = ?
AND CustomerID = ?"
; ~result = ExecuteSQL( ~query ; "" ; ""
; $customerID )
]; if ( ~result = "?" ; "" ; ~result )
go to layout (customers) // original
exit loop if ( get (foundcount) = get (recordnumber) // stop procees after last
got to records ( next )
I don't know your full set of table names and fields, so this is not going to work "as is".
Wow! I'm on my trip and don't have much time to work now, but I would like to learn the way that you described.
I have many questions...
FIrst: what is
Do I have to make a new table occurrence or just add new fields to my existing one?
What kind of fields are those, how to set them up:
PS. In my TABLE:customers I have many text fields such as address, telephone number, etc. I have a text field to describe a type of tax (25%, fixed, none). I have also a number field which describes FIXED tax amount (if tax type is different than "fixed" field is empty)
client_taxes is a new table for holding the taxes, since every year they can be different. the customerID is a foreign key, of course. If you store them like this, you have historical records.
The sumPerYear was the ExecuteSQL() that is run for each client at the end of each year. Set the field as I described (loosely) in my other reply.
The discountToApply is whatever you need based on the value of sumPerYear. Again, I don't know your fields, so used an example (which can be changed). Or how you will actually use that sum for that year.
So there is another question:
I have a lot of customers in my base (500). Only 50 of them have to pay taxes and have a unique category. Should I have a separate table for that group or is it enough to filter them with new table occurrence? And how to do that filter by category (with tax)?
by customerID is enough. if you need to add fields to the new table, then do so. If you need to filter by category, then do that.
How can I filter by customerID (auto-enter, serial number) ? It is different for every customer...
In different places of my database it could be useful to have filtered customers with specified category, but I don't know how to do that.
In the new table it is NOT auto-enter. It is a foreign key relating to the parent Primary Key (which is auto-enter). Re-read my full post.
Sent from miPhone
I try to explain my last question.
My table CUSTOMERS looks like this:
ID NAME CATEGORY TAX TYPE MONTHLY TAX VALUE
1 Leszek Merchant None (empty field)
2 Darłowo Shop 25% (empty field)
3 Elbląg Shop Fixed 1000,00
4 Gdynia Shop Fixed 1300,00
5 FCK Enterprise None (empty field)
6 Poznań Shop 25% (empty field)
In variuos places of my database it would be very useful to have only CUSTOMERS with CATEGORY "Shop". I think that I should set up a new table occurrency of my TABLE: CUSTOMERS, but I have no idea how to do it to have only "Shop" category in that occurrency.
I often use a value list of CUSTOMERS and I need only "Shop" category. Instead I have all of them and that makes searching much more difficult.
ok, so each CUSTOMER has either none (no tax to calculate) or percentage or fixed amount (per month)
This can still be used in a calculation for the final field I showed 'discountToApply'. So perhaps the name is in accurate, as it may be fixed-per-month and have nothing to do with the 'sumPerYear'. And that is ok. I leave it to you to create a Case() statement to get the value.
But you are now asking about "filter-by-category" and I see by your example, that you have the CATEGORY field/column. My question back would be where do you need to show this?
1) you can do a find in a List view that only show those for a particular category (by scripted find)
2) you can have a portal that relates to the customers with a "filter" (part of the portal dialog) that shows just that category
3) if you need a value-list, then you may need a relationship that sets up the list to show only that category.
Again, it all depends upon what you may wish to do with the data of that filtered/found set of records.
Every customer from group "with tax" has to give me an account book every month. I have a table where I sign every time I get a book from somebody. Table is simple:
Customer ID (fk)
When I add new record I describe a Year and pick a Customer from a list and in this moment I have all of my customers and I would like to choose only from those with category "with tax".