Max, Min, Count, are all standard Filemaker functions, documented in Help.
Yes these functions exist in Filemaker, but not in the same way they can be used in Access of Microsoft. You cannot insert a condition. In Access you can use the value of a field wich exists in the layout.
Can you give an example of what you want to achieve?
In a layout I have the following fieles : BuyCode (Primary key) ProviderCode (Numeric) ProviderSellCode (Numeric) In a new record when I give a value to ProviderCode or when I change the ProviderCode in aun existing récord , I should automatically get a value for ProviderSellCode. For the first buy from the provider, ProviderSellCode gets 1. For the next buy, it gets 2. The field ProviderSellCode depends of the provider and the quantity of buys from him.
If there is only one table, then create another Table Occurance of that table. If your table is called Buys then call the new TO, say:
Relate the original Table (Occurance) of Buys to this new Tabel Occurance by ProviderCode = ProviderCode
Set the field ProviderSellCode to be auto-enter by calculation:
Count ( buys_SelfByProviderCode ; BuyCode )
In this case, you could also set the calculation to be Max ( buys_SelfByProviderCode::ProviderSellCode ) + 1
Could you give me please the composition of the new table ? I do not understand very well how to make it.
Your answer is very interesting and I am impatient to try it but I need more information about the new table.
Thanks, best regards.
I tried as you said to me, but my field ProviderSellCode gets always the value 1, when it should get 1,2,3,... in the introduction of a new record for the same provider.
If I change the ProviderCode the field ProviderSellCode should change to a new value depending of the number of buys to this provider.
Did you understand my problem ? I am Spanish and I tried to use my best English to be in the Englis forum.
I used the spanish forum but nobody gave me a solution.
I used a different way here:
Note that there is only one table, but a second Table Occurance of the same table - I just duplicated the Buys Table and then linked it to itself.
I set the ProviderCode field to have a script trigger attached to it. When you enter the ProviderCode it commits the record (to update the number of records for that Provider) and then sets the count into the correct field.
If you try it the first way you'll see that it does work - after you commit the record. Until then the new record doesn't 'exist'. It is awkward to explain to the user that they have to 'click to commit' the record first, then modify the ProviderCode! The script trigger does everything in the right sequence.
You may have to build in a 'guard' - if you modify the ProviderCode again in a previous record it will update to a new value. You could change the script to check if a value has already been allocated, and if so do not change it.
Don't worry about the language - I understand you completely.
That is the first time I have used YouSendIt to link here - let me know if you get the file OK.
Hello, I got your database, it's working. I tried to do the same in mine but I get always the same value for all the records. I get 0 all times for the field ProviderSellCode. I do not understand why. I am very sad to see that these functions that I use very often in Access do not exist in Filemaker. Now I have difficulties to use in a script ExecuteSQL, I do not finf any good examples to know how to write the sentences. Best regards.
- duplicate the table
- link the original table to the new Table Occurance by the ProviderCode
- create the script to set the field
- attach the script to the ProviderCode fiels, as a Script Trigger
You can see all of those things in the file I sent.
I use the Max, Min, Average, and Count, functions very often, and they all work, so it will just be something small that is not right.
Is there anything else I can do to help?
A step by step on duplicating an existing table occurrence:
In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Note that in addition to the aggregate functions already discussed here, FileMaker has a second method for computing aggregate values--summary fields. Which method is best for a given problem depends on a number of design details in how you have modeled your data and designed your layouts.