jsword

It worked perfectly! Thank you. The FilterValues expression was what I was missing. Thank you for the help!

Hello! I am trying to write a simple calculation that is proving to be more difficult than I thought. I have a real estate database with the tables: properties, transaction, and people. A property can have multiple transactions associated with it, and each transaction has people attached to it (buyer, seller, broker). I am trying to flag all the properties (in the properties table) when a certain person is linked to any of the properties associated transactions, call this person "Bill". Taking it one step further, I would like to display a "C" when the Bill is associated with a transaction with the status of "Closed" and a "L" when Bill is associated with a transaction with the status of "On Market". That is easy enough and I have that portion working (in the transaction table). The problem is I want the "C" to be prioritized, meaning if Bill is associated with two or more of the associated transactions for one property, and one of those transactions status is "Closed", and the other is "On Market", I would like a "C" to be displayed in the property table. The way it is set up now, I can only seem to look at the most recent transaction (based on closing date) and display either a "C" or "L" depending on the status of the most recent transaction. Sometimes the transaction with the status "Closed" is more recent and vise versa. How can I set this up so that the calculation looks at all related transaction records, determines if any are flagged with either a "C" or an "L", and if there are both, display the "C"? Any help would be appreciated!

Great, thank you for the help! I will try to use this now and let you know how I fare.

Hello All! I am having trouble building a complex subsummary report efficiently for a real estate database. The report layout is simple: I basically have a layout with 18 columns, all years, 2000-2017. The layout only has subsummary parts, no body parts. The subsummary part I am using to display data is sorted by a field called Micromarket (basically a geographical area). I want to display the number of units built in each year for each Micromarket. I have the report working perfectly, but I had to add many fields to my property table to accomplish this. The fields are as follows: Field Name: Number_of_Units_2000: Calculation: If ( (Year_Built)=2000;Number_of_Units ) and a corresponding Summary field that is the "Total Of" Number_of_Units_2000. I have done this for each year on the report, so I have made 18 Calculation fields, and 18 corresponding Summary fields. I then put each Summary field in its corresponding column on the layout, and for each Micromarket, I am able to display the number of units added in a given year. Is there a more efficient way to do this? I have tried performing a different find for each year within a script, then populating a Merge Variable with the result, but the Merge Variable doesn't behave the same way on the layout as a Summary field does. Any help would be appreciated!