I have a status field in a table that is populated using a drop down list. From this status field I want to give users logging in a count of each status in the database. I'm a newbie and the only two functions that seem relevant are "count" and "exact". The values in the field are: "sold", "available" etc. The formula I was looking at was similar to:

count(exact("sold"; status))

but the field type is not appropriate for a text value such as "sold". Am I on the right track or is there something else?

Kevin

The exact() function is not needed here, but a couple relationships are.

Add two text fields with global storage and populate them with each status choice (one will have "Sold" and one will have "Available".) Then define two similar self-join relationships based on each global and the Status field:

Table1 <=> Table1 by Status Sold

Table1::gStatusSold = Table1 by Status Sold::Status

and

Table1 <=> Table1 by Status Available

Table1::gStatusAvailable = Table1 by Status Available::Status

Now define a calc for each status count:

Count of Sold (calculation, number result) = Count(Table1 by Status Sold::RecordID)

Count of Available (calculation, number result) = Count(Table1 by Status Available::RecordID)

Make sure the storage on those calcs is Unstored.

I apologize but I've only been able to implement the first part of your solution. I've created the global field in this case I made it a drop-list and then the self-join. Then I created a portal that displays all the records with the same status as the drop list. I just need the direction to convert from the drop-list selection to populate a text field with the number of records that have been selected based on the item selected in the status drop down.

Kevin

Add two text fields with global storage and populate them with each status choice (one will have "Sold" and one will have "Available".) Then define two similar self-join relationships based on each global and the Status field:

Table1 <=> Table1 by Status Sold

Table1::gStatusSold = Table1 by Status Sold::Status

and

Table1 <=> Table1 by Status Available

Table1::gStatusAvailable = Table1 by Status Available::Status

Now define a calc for each status count:

Count of Sold (calculation, number result) = Count(Table1 by Status Sold::RecordID)

Count of Available (calculation, number result) = Count(Table1 by Status Available::RecordID)

Make sure the storage on those calcs is Unstored.

Well, you are doing it differently than Ender suggested. But you can do it this way, if you want to show only one status at a time, instead of displaying a summary of all statuses simultaneously.

Since you seem to already have the global related to the status field, you only need a calculation field of Count(relationship::RecordID) to show the number of records matching the currently selected status.