◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by Jaymer »

USE CASE:
Even in a single-user, non SAAS system, a user may have some standard, repetitive queries that he needs to run frequently to see a desired set of Customer records.
ie. Customers with Balance > $250 AND DaysPastDue>60 AND State=WA, OR, or CA


Thanks to Tom Ford here

(Similar to what Dave (UnionSystems) was wanting in that thread, I had recently asked Vlad if there was a way for a Query to use a dynamically built string to specify columns. A Stored Procedure can bring back only a few cols. Would be nice if we could build a FIND string and have it used in a Query... but I digress)

OK, so seems like this is the beginning of Stored, User-defined criteria for Queries...
Allow a user to enter filter criteria to find a subset of data, saving that criteria, and performing that search again in the future.
In my database, I needed users with a MEGA rating and City='Roseville'.
These simple steps:
Screen Shot 2018-12-19 at 4.46.56 PM.png
Screen Shot 2018-12-19 at 4.46.56 PM.png (26.32 KiB) Viewed 15550 times
And the simple Query:
Screen Shot 2018-12-19 at 4.49.10 PM.png
Screen Shot 2018-12-19 at 4.49.10 PM.png (19.17 KiB) Viewed 15550 times

So all you need is a form mechanism to allow a user to enter Field criteria and construct the string to go After the hardcoded "FIND LEAD WHERE " part. The new EXEC_STRING function added in Apr 2018 (ver 8.1) allows this to happen.
(Note that in my query, I needed to restrict data to only that Tenant's data so there is a criteria before the User's criteria.)
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by tford »

Great work, Jaymer ,in putting together these puzzle pieces!

When you put this concept together with others, it will have even greater synergy:
1- COUNT function of instances in the resulting query
2- SUM function of instances in the resulting query
3- Email to of items in the resulting query
4- DISPLAY DOCUMENT to Word Merge instances in the resulting query using User Defined Documents
5- Dynamic Visual Perspective (Dashboard) with stats calculated based on the selected User Defined Saved Query

I'm sure there are a lot more, but these pop into my mind as no brainers to build on Jaymer's concept.

A while back, I gave Jaymer a copy of an old BSV with very crude User Defined Tag & Saved Query capabilities. It was very clunky & inefficient, but worked. I can now see that User Defined Tags could also work very well with Jaymer's Save Query masterpiece to put more of the power of AwareIM in the end users hands.
Tom - V8.8 build 3137 - MySql / PostGres
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by Jaymer »

thx Tom
The :idea: really clicked ON when you wrote in that other post:
1) Execute your dynamically built string, like: EXEC_STRING `FIND Person WHERE Person.Surname='Smith' `
2) INSERT the resulting instances of Person BO into LoggedInSystemUser.Selected_Persons
3) DISPLAY TEST (where TEST uses FIND Person WHERE (Person IN LoggedInSystemUser.Selected_Persons)

I sat there and thought about that and said 'Hmmmm'.
# 3 threw me - I actually went to a query and typed that in - had not seen "uses" before
and then it all came together.

I'm already doing #3 from your list - but I build a list of pointers from a user doing Filters and checkboxes to select which ones they want, then a Panel Op to "Add selected to List".
That has several limitations that this NEW method overcomes.

I already have User Defined Tags working, so that PLUS Saved User Queries adds 2 nice features.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
UnionSystems
Posts: 197
Joined: Fri Jun 17, 2016 7:10 am
Location: Brisbane Australia
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by UnionSystems »

Thanks Tom and Jaymer for your advice.

I've been using the

Code: Select all

INSERT the resulting instances of Person BO into LoggedInSystemUser.Selected_Persons
method to collect result into a query for a few months but the problem I always get is when the results of the FIND are more than 1,000 it starts to take too long for all the INSERTs to complete. I have queries that FIND 50k+ rows and they really are too slow AND kill server performance.

My objective is to enable users to define relatively complex bespoke FINDs, save those FINDs and be able to browse and/or export the results quickly. I've had them building FINDs for a while and storing them and executing with EXEC_STRING and I have also been creating pm_Object relationships to "store" the results (but again when they are > 1k rows the are SLOW).

I'm contemplating exploring use of REST output from AwareIM as a way to get the data quickly...only other option is perhaps exporting the FIND results into an AwareIM Excel document and giving that to the user?

The later seems the most natural concept but it would require dynamic alteration of Excel columns???
AWS Linux, Windows Server, AIM 8.4 & 8.6
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by tford »

I'm contemplating exploring use of REST output from AwareIM as a way to get the data quickly
Can you please share more about this idea?
Tom - V8.8 build 3137 - MySql / PostGres
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by tford »

FIND are more than 1,000 it starts to take too long for all the INSERTs to complete
Thanks for the tip about this.

I have not experienced that issue yet as my main application deals with a relatively small number of records. Good to know for future development though!
Tom - V8.8 build 3137 - MySql / PostGres
UnionSystems
Posts: 197
Joined: Fri Jun 17, 2016 7:10 am
Location: Brisbane Australia
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by UnionSystems »

tford wrote:
I'm contemplating exploring use of REST output from AwareIM as a way to get the data quickly
Can you please share more about this idea?
Concept is :
  1. Enable user to create unique FIND statement and store that in object
  2. use AwareIM REST exposing facility to EXEC_STRING that FIND statement returning results as JSON
  3. Expose that JSON to user as a data grid or a downloadable CSV
AWS Linux, Windows Server, AIM 8.4 & 8.6
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by tford »

Are you saying the FIND works be executed faster on that scenario?
Tom - V8.8 build 3137 - MySql / PostGres
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by Jaymer »

1. I noticed an unacceptable delay in just finding hundred records.
2. But the issue is not with the find portion, if you use the logger, you can see it’s the insertion of n individual records Into the ref table, and Rules being evaluated for each Modification of the parent/owner BO

Syntactically, we are basically building an Aware FIND statement
If we built a MSSQL- or MYSQL-compatible statement, then this can easily be done on the backend and those thousands of records could create REF table records in one or two seconds
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by Jaymer »

tford wrote:Are you saying the FIND works be executed faster on that scenario?
This will work faster, because we’re not having to physically create the rows in the database in that reference attribute.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by tford »

I wonder how much of that extra time is due to rules firing on LIRU during the INSERT process
Tom - V8.8 build 3137 - MySql / PostGres
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by Jaymer »

tford wrote:I wonder how much of that extra time is due to rules firing on LIRU during the INSERT process
yes, thats what i meant by owner/parent.
Since the LIRU/RU is the owner of "om_Leads", then each time one is added to that list, you can see stuff being evaluated.
Attachments
Screen Shot 2018-12-21 at 5.06.36 PM.png
Screen Shot 2018-12-21 at 5.06.36 PM.png (39.29 KiB) Viewed 15491 times
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
UnionSystems
Posts: 197
Joined: Fri Jun 17, 2016 7:10 am
Location: Brisbane Australia
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by UnionSystems »

I've had a win on this and my "solution" does not use EXEC_STRING or create child records and gives me the required output quickly using an AwareIM document template :-)

This is the UI I 've had for a while that allows the user to define and save their FIND criteria. My struggle has always been creating the output.
Screen Shot 2018-12-23 at 11.19.54 am.png
Screen Shot 2018-12-23 at 11.19.54 am.png (222.02 KiB) Viewed 15463 times
I'll focus on this new technique of getting output. The object in the screen above is Finder

Finder Attributes

Finder.Find_Statement is the FIND I assemble from the "Conditions" panel in the screen shot. I have a process to assemble the FIND but you could also simply type it in raw text.
Finder.Find_ListTable is an attribute containing construction of a LIST_TABLE() function (described in AwareIM Manual)

Finder Rule

This rule assembles Finder.Find_ListTable

Code: Select all

Finder.Report_ListTable='<< LIST_TABLE(`'+Finder.Find_Statement+'`,`'+LIST_LINE(Finder.pm_Finder_Attribute_Output,'','`,`','Attribute_Name')+'`)>>'
In the example from the screen shot the resulting value of Finder.Report_ListTable is

Code: Select all

<< LIST_TABLE(`FIND Person WHERE Person.sc_MemberState_ActiveMember='Yes' AND ( Person.sc_PersonCategory_Abbreviation <> 'NON' AND Person.sc_PersonCategory_Abbreviation <> 'NMA' AND Person.sc_PersonCategory_Abbreviation <> 'OHS' AND Person.sc_PersonCategory_Abbreviation <> 'SSTUWA staff' AND Person.sc_PersonCategory_Abbreviation <> 'ETC') AND Person.isRep<>'Current' AND Person.isDeputyRep<>'Current' AND Person.isOSHRep<>'Current' AND Person.isWomensContact<>'Current' AND Person.DateOfJoin<'2018-12-01' AND Person.EmailAddress IS DEFINED AND Person.sc_PersonCategory_Title<>'Student' ORDER BY Person.Surname, Person.FirstName IN BATCHES OF 100`,`PhoneHome`,`FirstName`,`PhoneWork`,`Number`,`EmailAddress`,`Surname`,`sc_PersonCategory_Abbreviation`)>>
Finder_Report Document Template

Then I have a small Document Template called Finder_Report of type HTML that only contains

Code: Select all

<<Finder.Report_ListTable>>
Export Document Process

Then I have a process that take this Finder as Input. The key Action is simply an EXPORT DOCUMENT

Code: Select all

EXPORT DOCUMENT Finder_Template TO FILE SystemSettings.Finder_ReportPath+SystemSettings.FolderChar+Finder.Report_FileName+'.xls'
It creates a file from the Finder_Template with a HTML table of all results embedded in it. The file has an .xls extension even thought it is HTML and Excel seems happy to map HTML table cells to spreadsheet cells. The process takes around 10 seconds to create 15k rows.

The only problem I'm seeing is that I cannot control the order of the output attributes.

What surprises me about this is how the

Code: Select all

<<Finder.Report_ListTable>>
actually gets "executed" by AwareIM twice. It both inserts the <<Finder.Report_ListTable>> and acts on the LIST_TABLE() that we have in that attribute. Not sure what it does first but it works!

You could also use LIST_LINE in place of LIST_TABLE to create a CSV file. Only issue there is escaping quotes and commas in your data.

Happy holidays everyone :-)
AWS Linux, Windows Server, AIM 8.4 & 8.6
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by tford »

Here is a BSV demonstrating Tags and Saved Searches without inserting the instances of the target BO in LIRU. The only thing stored in LIRU are the Tags being searched.

https://www.awareim.com/forum/viewtopic.php?f=4&t=10826
Tom - V8.8 build 3137 - MySql / PostGres
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

Post by johntalbott »

Here is quick video of a proof of concept to save a search by saving the grid filter values that can then be reapplied. It's a client side script that leverages the Kendo UI API to save and apply the filters.

For the purpose of this poc, the filter values are saved in memory but the code could be enhanced to save any number of filters to the database, display a list of the filters that can be selected and applied to the grid.

https://screencast-o-matic.com/watch/cqVVV7rRQV]
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
Post Reply