How have you related the two tables?
Warehouse::Warehouse # = Contracts::Warehouse #
You need to perform a find on Warehouse as you need to find all warehouse records that meet either criteria 2 (no contract) or 3) Contract has Termination date < today's date.
It depends on whether or not Contracts contains multiple (past) contracts for the same warehouse or not. If it doesn't, simply search for <= today's date in the termination date field from your warehouse based layout.
If you do have multiple contracts records for a given warehouse, here's an approach that will work in that case: Define an unstored calcuation field, cVacancy in Warehouse as Last ( Contracts::Termination Date ) < Get ( CurrentDate )
Clear the "do not evaluate if all referenced fields are empty" check box and search for 1 in this field.
Thanks for the reply.
Yes the tables are related with the Warehouse # field.
Each warehouse could have multiple contracts associated with it (one renter terminates and then a new renter rents)
Not sure how the unstored cVacancy field will give the complete list. This will just give me the warehouses with termination dates correct?
Sorry so ignorant.
If there is no contract at all, Last ( Contracts::Termination date ) returns null. With "do not evaluate..." cleared, the expression then returns True (the number 1) as null < Get ( CurrentDate ) evaluates as a true statement.
If there is a termination date, the expression only returns true if the Last related Contract record's termination date is less than today. Thus it works for both situations. This approach assumes that the relationship between these two tables are either unsorted (the default) or are sorted in an order that makes the most recent contract record for a given related warehouse record the last warehouse record.
What kind of field would cVacancy be?
I guess I then create a query for "1" in the cVacancy field and then create a report based on the results of the query. Since I am new to FMP, I'm sure I will need some hand holding through this process. I hope you don't mind.
Thanks again for your assistance.
I see the field answer "calculation". Sorry for that.
Field type calculation with a return type (drop down inside specify calculation dialog) of number.
I created the cVacancy field as you described.
When I look into the warehouse table (ware#), each unit comes back with a "1". Even the units that have contracts made for them. What do you think I am doing wrong?
Can't tell much from here.
Even the units that have contracts made for them.
What are there termination dates? It should return 1 if the most recent contract record has a completion date < today's date.
None of the units have termination dates. I have entered in sample contracts and they all have beginning contract dates but no termination dates. I figured those would return a "0" in the cVacancy field.
Muy Bad, Empty termination dates will also evaluate as true--which means this won't work as designed.
IsEmpty ( Last ( Contracts::Warehouse #) ) or
( not IsEmpty ( Last ( Contracts::Termination Date ) ) and ( Last ( Contracts::Termination Date ) < Get ( CurrentDate ) ) )
The last code worked. Thanks.
Now here is where you know that I am new.
I started a new report based on the Warehouse data. I know I need to use the cVacancy as the filter. Show only units that have a cVacancy = 0. Could you also give me some pointers on how to do this.
Thank you so much for your help.
You can perform a find either by hand or in a script to bring up the records you want. You can use a script trigger to perform this each time you select this layout by using the onLayoutEnter script trigger.
A scripted find for this:
Enter Find Mode //clear the pause check box
Set Field [Warehouses::cVacancy ; 0 ]
Set Error capture [on]
Sort [no dialog restore]
I tried to enter what you had verbatim but it wouldn't let me type your code in. I created a script named "Vacancy Report" when I created the vacancy report. FM automatically added code. I enter your info in and it gave me eventually all the used units (those with a cVacancy value of 0. I changed your Set Field line from a 0 to a 1. However, that didn't solve the problem. Here is my code exactly:
Go to Layout ["Vacancy Report"(Warehouses)]
Enter Find Mode 
Set Field [Warehouses::cVacancy; 1]
Set Error Capture [On]
Perform Find/Replace [Warehouses::cVacancy = 1; Find Next]
Sort Records [Restore; No dialog]
Enter Browse Mode 
If [GetAsNumber ( Substitute ( ApplicationVersion ); "."; "x")) < 1000]
Enter Preview Mode [Pause]
Enter Browse Mode 
Go to Layout [original layout]
Can you help me? Sorry for being a novice.
I removed my perform find replace statement and added the perform find statement you wanted. It works great. Thanks.