Hi, I have a Table of records called "Jobs". These jobs have a field called "Status". Status has 4 entries:

1. Pending Move In

2. Renting

3. Pending Move Out

4. Moved out

I would like to have a ratio calculated that shows my "Pending Move Ins" minus my "Pending Move Outs".

So if I have 5 pending move ins, and 4 pending move outs, I'm +1. I need to know how to calculate this basically:

Count the Number of records whose status is "pending move in" - the count of records whose status is "pending move out".

I'm sure it's not a difficult process, but I'm struggling to figure it out. Thank you in advance for all the help!

Anybody have any ideas on this one? Thanks.

You could define a calculation field (result is Number) =

Case ( 

Status = "Pending Move In" ; 1 ;

Status = "Pending Move Out" ; -1 

)

then use a summary field to total the calculation field.

You could define a calculation field (result is Number) =

Case ( 

Status = "Pending Move In" ; 1 ;

Status = "Pending Move Out" ; -1 

)

then use a summary field to total the calculation field.

Ok, thank you, that's very helpful. One question though, is there a way to have that without recalculating everytime I sort the records? When I click show all records, it tells me what I want, but when I sort by the status, it recalculates based on the current sort...

Thank you again.

Do you mean "find" rather than "sort"?

Do you mean "find" rather than "sort"?

Yes, I have 4 buttons at the top of my database and they are Pending Move In, Renting, Pending Move Out, Moved Out.

That way if I'm focusing on all my current move ins, I can see those records, etc. If I show all records it works great. Is there a way I can have the formula calculate based on all records and not just the sorted ones? Thanks.

To summarize all records in the table, regardless of the found set, you will need a relationship. This can be a self-join defined as:

Jobs::Status x Jobs 2::Status

(note the x relational operator). Then use a calculation field =

Sum ( Jobs 2::cStatus )

instead of the summary field (where cStatus is the calculation field described above).