AnsweredAssumed Answered

ExecuteSQL help

Question asked by KrisYells on Apr 30, 2016
Latest reply on May 2, 2016 by beverly

HI,

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.

 

example:

Property IDDocuments ReceivedDocuments SignedDocuments Mailed
5015/1/20165/2/20165/3/2016
502
5035/2/2015

 

 

thank you

Outcomes