It was recently suggested to me I try to use Execute SQL and Virtual List technique to produce my crosstab report. unfortunately my situation is different than all the training material and tutorials I have been working with. Can some one help with how the Execute SQL should be set up?
I have 2 tables joined by a property ID field. The parent table is a list of properties ID's. The child consist of multiple records entered via a portal from the parent table. The child records are events that include the actual event and the date of the event. The event field is picked from a value list so it's the same field, different records. An Event can be Documents Received, Documents Signed, Documents Mailed. I have 160 different events that could happen on a property. Not every property will have every event.
In the past, If I pull a crosstab list view from the parent table it will only give me the first event entered for the property.
or if I pull it from the events table it will give me correct event result, it wont include all the properties that don't have any events.
My boss will come to me and say I need all the properties in a list showing these 3 events. I have all this in a spread sheet so getting her the report is no problem. I would like to solve this issue so I can commit fully to Filemaker.
|Property ID||Documents Received||Documents Signed||Documents Mailed|