calculation lookup

[superlukeyboy]

i have four databases at the moment. 'users', 'dvds', 'lists' and 'orders'. in the database users i have a field called 'orders made'. i want it to be able to search my database 'orders' for the name 'luke nunn' which will be in a field called 'name'. after it has gone through the database and counted all this i want my 'orders made' field to display how many there were. is there a script i can use or is it a formula i need to put in the calculation bit? help needed!

Version: v6.x

Platform: Mac OS X Jaguar

[Ender]

If you have a relationship between Users and Orders, you can have a calculation field defined in Users that gets a count of related orders:

Order Count = Count(Orders::Order ID)

If you have more complex matching criteria, do something like this:

in Orders:

Active Order = if(isempty(Completed Date), 1, 0)

in Users:

Active Order Count = sum(Orders::Active Orders)

[CobaltSky]

Hi,

The suggestions that 'Shadow' has made are on point as regards the type of formulae you might use for this.

The other part of the puzzle is the relationship that the formula uses to access the records it is counting or summing. You've not said, but I'm guessing that if you have any relationship between the 'users' and 'orders' files at present, it is not based on the name field.

Based on your description, you will require that the relationship is one which matches a name field in the 'users' database with the name field in 'orders'. If your name field in orders is in fact defined to automatically look up values from a corresponding field in users, or if for other reasons you can be confident that these values in the two files will always match, then you could create a relationship from users to orders which matches the existing name fields. Otherwise, you'll have to create a name field in users that presents the name in the same format as it will appear in users.

Once you have, by whatever means, created a relationship which matches on name, then the calc approach will work (note that you will be referencing the field being counted or summed via this relationship.

There is however, also a performance consideration. Fields which reference related data (as in this case) must be unstored and are therefore required to be recalculated each time they are displayed. If you frequently have many orders per user, then calculating the number of orders may introduce a perceptible delay into the display of layouts in the users file which include tha calc field. On a list layout in particular, the delay may be long enough to be problematic.

In this situation, you might wish to consider having the value retrieved via script (using similar calc formulae) and stored in the users db, with a 'refresh' button that allows you to re-calc the count at will. This will mean that the users will only be slowed down by the recalculation of order counts when they actually need the data - but of course the trade-off is that the values displayed will not always be up-to-the-minute.

[superlukeyboy]

i rele dont understand any of what you two just said, i tried to do what Shadow said but it came up with all sorts of errors so that didnt work. and i really havnt got a clue what CobaltSky was going on about, as you can probably tell i am very inexperienced with filemaker, i used to use access and this is a whole new world. these are my databases, i want the field in "Users" called "Orders Made" to look at the field "User Name" and search the database "Orders" for it. At the moment, in the "User Name" field, it says "Luke Nunn". i want the "Orders Made" field to search for "Luke Nunn" in the orders database. after it has done this i want it to show how many times "Luke Nunn" was found. im so confused

[CobaltSky]

Hello superlukeyboy,

Attached is a copy of your file which has the required relationship and calculation in it and working.

Whilst it's unfortunate that you weren't able to figure out what I was 'going on about', it would advance matters more if you actually said what words or phrases were presenting a problem. That way there would be a chance that someone might provide clarification, rather than offering you yet more advice that you can't use.