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:
And the simple Query:
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.)
◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
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
Aware Programming & Consulting - Tampa FL
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
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.
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
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
thx Tom
The 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.
The 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
Jaymer
Aware Programming & Consulting - Tampa FL
-
- Posts: 197
- Joined: Fri Jun 17, 2016 7:10 am
- Location: Brisbane Australia
- Contact:
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
Thanks Tom and Jaymer for your advice.
I've been using themethod 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???
I've been using the
Code: Select all
INSERT the resulting instances of Person BO into LoggedInSystemUser.Selected_Persons
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
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
Can you please share more about this idea?I'm contemplating exploring use of REST output from AwareIM as a way to get the data quickly
Tom - V8.8 build 3137 - MySql / PostGres
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
Thanks for the tip about this.FIND are more than 1,000 it starts to take too long for all the INSERTs to complete
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
-
- Posts: 197
- Joined: Fri Jun 17, 2016 7:10 am
- Location: Brisbane Australia
- Contact:
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
Concept is :tford wrote:Can you please share more about this idea?I'm contemplating exploring use of REST output from AwareIM as a way to get the data quickly
- Enable user to create unique FIND statement and store that in object
- use AwareIM REST exposing facility to EXEC_STRING that FIND statement returning results as JSON
- Expose that JSON to user as a data grid or a downloadable CSV
AWS Linux, Windows Server, AIM 8.4 & 8.6
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
Are you saying the FIND works be executed faster on that scenario?
Tom - V8.8 build 3137 - MySql / PostGres
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
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
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
Aware Programming & Consulting - Tampa FL
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
This will work faster, because we’re not having to physically create the rows in the database in that reference attribute.tford wrote:Are you saying the FIND works be executed faster on that scenario?
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
Aware Programming & Consulting - Tampa FL
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
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
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
yes, thats what i meant by owner/parent.tford wrote:I wonder how much of that extra time is due to rules firing on LIRU during the INSERT process
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 (39.29 KiB) Viewed 15654 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
Jaymer
Aware Programming & Consulting - Tampa FL
-
- Posts: 197
- Joined: Fri Jun 17, 2016 7:10 am
- Location: Brisbane Australia
- Contact:
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
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.
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
In the example from the screen shot the resulting value of Finder.Report_ListTable is
Finder_Report Document Template
Then I have a small Document Template called Finder_Report of type HTML that only contains
Export Document Process
Then I have a process that take this Finder as Input. The key Action is simply an EXPORT DOCUMENT
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 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
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.
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')+'`)>>'
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`)>>
Then I have a small Document Template called Finder_Report of type HTML that only contains
Code: Select all
<<Finder.Report_ListTable>>
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'
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>>
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
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
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
https://www.awareim.com/forum/viewtopic.php?f=4&t=10826
Tom - V8.8 build 3137 - MySql / PostGres
-
- Posts: 619
- Joined: Wed Jun 17, 2015 11:16 pm
- Location: Omaha, Nebraska
- Contact:
Re: ◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING
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]
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
We specialize in enabling business through the innovative use of technology.
AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t