FR - Linked Queries

On this forum you can see a list of new features requested by users and you can also cast your own vote (you need to login to vote).
Post Reply
PointsWell
Posts: 1473
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

FR - Linked Queries

Post by PointsWell »

At the moment you can link a query output to another panel to give that panel a BO context

This works OK as long as you have only two queries, eg a list of companies which if you select a company instance might show a list of employees at that company.

You can also run queries that prompt the user for a value and then show the results.

Code: Select all

FIND Company WHERE Company.Name=?Name
I'd like to be able to extend that runtime prompt to link a query to another query's selected record

Code: Select all

FIND Employee WHERE Employee.Company=SELECTED(Companies)
Where SELECTED() captured the selected record of the query Companies. This is not the same as a sub query at the moment because a sub query is an encapsulated query within a query whereas this is two distinct queries that I need to display their results side by side, one driving the other, that would easily be extendable to further chains

Eg Companies -> Employees -> Telephone Numbers

CompanyQuery

Code: Select all

FIND All Company
EmployeeQuery

Code: Select all

FIND Employee WHERE Employee.Company=SELECTED(CompanyQuery)
TelNoQuery

Code: Select all

FIND TelNo WHERE TelNo.Employee=SELECTED(EmployeeQuery)
You can get round this limitation using the LIRU or the Session variable, however this generates an SQL query EVERY time the LIRU is called and the query selects everything in that BO hierarchy. This seems wasteful if the LIRU is searching all the way down a Company BO structure while you are working on an unrelated BO eg Orders. (I am assuming the session variable BO works in the same way as the LIRU I haven't tried it yet but I can't really think of a way that it would work otherwise). I have 4 core BOs that support quite complex parent child relationships so putting these objects onto the LIRU generates a lot of SQL activity when the results are not needed.

If you were able to generate linked queries similar to that described above, you'd be able to design VP with multiple related parent child queries without having to engage highly complex refresh processes eg multiple different queries hanging off the selected Company BO.

While I write this I am questioning how it might capture the use of the same query on two different tabs... but that is solutioning not suggesting.
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: FR - Linked Queries

Post by tford »

This seems wasteful if the LIRU is searching all the way down a Company BO structure while you are working on an unrelated BO eg Orders.
"... and the query selects everything in that BO hierarchy."

Showing my ignorance here. I don't understand these parts of your post.

I use LIRU / LISU extensively & and would like to fully understand the downsides and alternatives.

For example, I often use a RU form to have the user select a variety of parameters .. for example LoggedInSystemUser.Chosen_Company & LoggedInSystemUser.Chosen_Employee.

And on the form for selecting Chosen_Employee, I use a filter to only show Employees of Chosen_Company IF Chosen_Company is selected, otherwise the user can select ANY Employee as Chosen_Employee

The "Chosen" attributes are used in queries like:
FIND Transaction WHERE Transaction.Company=Chosen_Company
FIND Emoloyee WHERE Employee.Company=Chosen_Company
FIND Timecard WHERE Timecard.Employee=Chosen-Employee
Tom - V8.8 build 3137 - MySql / PostGres
PointsWell
Posts: 1473
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: FR - Linked Queries

Post by PointsWell »

What I mean by the BO hierarchy is

Suppose
Contact om Addresses
Addresses ps Country
Addresses ps AddressType

Contact om Orders
Orders om OrderLines
OrderLines ps Product Items

Let's imagine that you use LIRU to store Contact to keep this in Context for a query.

If you turn on SQL logging you can see in the background when you move about your app that Contact is constantly being called. If you move to you Inventory page in my made up thought experiment you will see a select statement run that goes from the Contact record all the way to the bottom of the hierarchy including the psCountry, psProductLines etc.

Now imagine you've used LIRU.ChosenCompany LIRU.ChosenEmployee to store instances of those Contact records. The SQL is now twice the chain from Contact to the lowest level BO. This SQL fires if the BOs in the LIRU are set to UNDEFINED. This means if you have a query on one tab that you are using one BO attached to the LIRU to maintain context and you use it only for that query you are still dragging around the weight of that BO all over you apps. If you have not switched off Check when referred on your business rules you then carry the added weight of the rules firing on each BO as it is retrieved in the massive SQL query.

There are some things that are absolutely relevant to put in the LIRU, for my app I have a separate LIRU and RelatedContact record (users are transitory, but the transactions that they generate need to exist after they leave the company), the Tenant record that the LIRU belongs to is also a relevant BO as that controls access to everything else.

Single parameters e.g. a search string are trivial, it is when you start sticking BOs into the LIRU things become less efficient.

If a query that uses a BO as a filter in the example I gave above, it is wasteful to place in the LIRU.

Instead of placing the BO in the LIRU I only put BO IDs the downside of this being that if you place the ID in LIRU you need to find the BO by ID but if you are only searching for it at the time you need it then you save a huge amount of LIRU SQL requests - you also avoid the business rules running if you forgot to switch off the Refered usage. If you need more than item from your LIRU I stick all the bits that I use, eg LIRU.ContactID, LIRU.ConractName, It's less convenient than having the entire BO at hand when I decide I need it, but I get round that by having a process that sets all the attributes that I want and call it whenever I touch the BO I want, so instead of a line that says LIRU.Contact=Contact I call a process SetLIRUContact which contains LIRU.ContactID=Contact.ID etc
PointsWell
Posts: 1473
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: FR - Linked Queries

Post by PointsWell »

Another alternative to Linked Queries might be to be able to assign a BO to a Tab that exists only when that instance of a Tab is present. This would overcome an issue that I am having with having an in focus tab for Contacts opening from a general page of Contact records.

If the process launched with the Contact attached to the Tab and that Tab context contact being available to the queries on the page, it might have a similar effect as the SELECTEDRECORD(QueryName) I described above.

For example
FIND Employees WHERE Employee.Company=TABCONTEXT(Company)


Last bumped by PointsWell on Wed Sep 30, 2020 7:48 am.
Post Reply