◄BREAKTHRU► User Defined SAVED Queries EXEC_STRING

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

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.

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.

UnionSystems

Thanks Tom and Jaymer for your advice.

I've been using the

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???

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?

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!

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 :

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

tford

Are you saying the FIND works be executed faster on that scenario?

Jaymer

I noticed an unacceptable delay in just finding hundred records.
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

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.

tford

I wonder how much of that extra time is due to rules firing on LIRU during the INSERT process

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.

Screen Shot 2018-12-21 at 5.06.36 PM.png

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

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

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

<< 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

<<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

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

<<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 🙂

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.

Sample BSV: Tags and Save Search Demo / Proof of Concept

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]

tford

You're hilarious, John! "Just tinkering around" ... "Not super sophisticated"

Amazing work!

UnionSystems

What you have demonstrated is gold!

My customers already like the inbuilt filtering in AwareIM queries. They would be extremely happy to be able to store those filter values into an AwareIM Object they could recall later Would it be possible to include the Query “Save State” data as well (ie so columns can be enabled/disabled and the column widths saved)?

Jaymer

Since John has done the proof of concept part, shouldn’t it be up to support to implement this?

UnionSystems

What John has done in his demo video is fantastic.

John would it be possible to include saving of the state of the query at the same time (so that users could define what columns are included)?

Being able to save these as AwareIM objects so the client could recall them as a later time would be very very nice.

johntalbott

UnionSystems wrote
What John has done in his demo video is fantastic.

John would it be possible to include saving of the state of the query at the same time (so that users could define what columns are included)?

Being able to save these as AwareIM objects so the client could recall them as a later time would be very very nice.

Yes