Select Multiple

[Ron Cates]

Hi All. I've been learning and utilizing ExecuteSQL all over the place and here's my latest endeavor.

I'm trying to use ExecuteSQL to grab a list of record IDs based on selections in global fields to be used as a multikey for a relationship .

So in a global field called gEmpIDs which is related to the ID field of my Employees table I have the following calc

 

Let ([

~status = Case (
                gEmpStatus = "Active" ; 1 ;
                0 )

];

ExecuteSQL (
"
SELECT ID FROM Employees
WHERE status = ?
"
;"";""; ~status )

)

gEmpStatus is a dropdown with selections for "Active" and "Inactive"

status is a field in Employees that contains either 1 for active or 0 for inactive

This works fine and it's a start, but now I would like to add a selection to the dropdown for "All"

So my question is how would I adjust the calc to Select both active and inactive ( 1 and 0 ) when I gEmpStatus is set to "All"?

Thanks in advance for any help

[Wim Decorte]

Bring the building of the SQL syntax itself into the Let() part and branch based on the ~status.  If ~status = all, "select id from employees"

[Ron Cates]

Thanks Wim. I was just working along those lines and came up with this

 

Let ([

~status = Case (
                gEmpStatus = "Active" ; 1 ;
                gEmpStatus = "Inactive" ; 0 ;
                gEmpStatus ) ;
~statusQuery = Case ( ~status = "All" ; "" ; "WHERE status = ?" ) ;
~query = "
SELECT ID FROM Employees
" & ~statusQuery & "
" ;

~result = ExecuteSQL ( ~query ;"";""; ~status )

];

~result

)

 

Not sure how elegant it is but it works and it's a start to build on.

[Ron Cates]

So the idea of trying to learn how to use ExecuteSQL for essentially building filtered portal relationships is so I can use the technique for a multitude of different situations. As I think of all the different scenarios I would like to apply it to it seems to get pretty complicated. Here is an example.

I have a table of Employees with a status of 1 or 0

each employee can be assigned to one or more companies being tracked from the Companies table. The department each employee belongs to is based on the role they play in each company assigned, therefore department is set in the Employees Companies join table. An employee can be in one deparment for one company and another department for another company.

So now I'm looking at building a calculation that will be able to adjust for the following selection options for who to view in the portal

*All or selected Company

*All or selected Department

All or selected Status

*adds the additional complexity of needing an Inner Join

So the question I guess would be is there somewhere someone can point me where I can learn a technique for building ExecuteSQL statements with this kind of flexibility and complexity?

Thanks

[Wim Decorte]

One big note of caution: be careful with ExecuteSQL() calls in calculations.  Especially if you more than say 10,000 records in the table you are querying.  There is a very real penalty if the ExecuteSQL() fires while the user has an open record in the that target table.  FMS will send the client ALL the data.

If you did your call in a script then at least you'd have a chance to commit the records.

I'm working on a blog post that demonstrates this.  It was part of my 2014 devcon session.

 

[Ron Cates]

I would be very interested in reading that. I have noticed some situations where I've used ExecuteSQL() on large target tables in a multiuser system where the behavior seemed to be a little sketchy. I've been developing my solution for the past seven years and it has become very complex over time building new functionality on top of old.  I thought maybe it had more to do with that. I started out like many, fumbling through the beginner tutorials to start and I have relied heavily on this forum to build my knowledge. I haven't been very active on the forum over the years but I have been on here all along reading and learning from all the great developers here like you, Michael, Loretta and so many others . Sometimes I feel like I almost know some of you. Does that sound creepy? Lol!  I am extremely grateful to all of you who volunteer your time to help out us developing developers. I hope to meet some of you at DevCon one of these years. The boss is so close to sending me. But alas it doesn't look so good for this year. Maybe next year..

But I digress. Moving forward, I've been given the go ahead to finally rebuild the whole solution from the ground up to take advantage of all that I have learned over the years. I'm in the process of trying to decide on techniques to use as standards for the entire build. I have been looking pretty heavily into ExecuteSQL() and so am hoping to understand and be aware of any pitfalls to watch out for.

thanks again for your input WIM and I'll be looking forward to reading that blog.

[Wim Decorte]

Here you go: http://www.soliantconsulting.com/blog/2016/04/bag-goodies-executesql-named-buckets-relationinfo