Hi @durette 

Since we are now moving to a cloud-first architecture we want to move away from direct access to the DB. Writing raw SQL against our tables/views is not a recommended pattern anymore either.

The Query Designer introduced from 21R2 is an important feature in that direction allowing power users to “design” queries against our entity model instead of “writing” SQL queries directly against our DB objects.

The queries designed using the Query Designer would typically expose a REST API that can be used as a read-only data source.

We have used and seen the Query Designer, it is not nearly developed enough to use for our purpose.

We’ve sent all of our quick reports and some adhoc queries to the IFS product design team who are responsible for the query designer, they have confirmed that the Query Designer is not far enough along yet for it to be a complete replacement for the SQL Query Tool.

IFS have made several decisions like this regarding upgrade customers where necessary tools were deprecated before a suitable replacement has been designed.  We have no choice but to use the SQL against table/views to bridge this design deficiency introduced with Cloud until IFS have caught up with the needs of customers using the software to run a business.

Just to add my two cents with regard to, “...we want to move away from direct access to the DB. Writing raw SQL against our tables/views is not a recommended pattern anymore either.”:

I agree with not writing data to IFS via “raw” INSERT or UPDATE statements, but using the Package API calls is 100% safe (and always should be, right?). The Packages provide all the same logic that IEE and Aurena end up using anyway, and that PL/SQL provides the foundations to just about everything.

I would not be able to do our data migrations without direct access to the database, as there have been things I could not get working via the REST API, and even if I did, I imagine performance would be not-good. The Data Migration Toolkit is not flexible enough to run migrations where the spec is fluid and lots of changes have to be made rapidly. The MTK is not very good at, “reset data and do it all again”, so we’d be doing multiple clones per day to achieve the iterations we need.

Thanks,

Joe Kaufman

@Rifki Razick 

Please help convert me to the new way of thinking in IFS Cloud.

Here is a normal everyday query from a single user, how is this done in Cloud on the Customer Order Line overview without creating a Quick Report.  If Query Designer can do this, the user then wants to move from the Line view to the Customer Order view for more information, that can be done in one click today with IEE.  How is that done in Cloud?  Something that is done in about 30 seconds directly on the view in IEE, but I haven’t figured it out in 30 minutes in Cloud.

SQL Where Expression

DATE_ENTERED > '12/31/2020' and CATALOG_NO in ('2450-300','2450-330')  and STATE <> 'Cancelled' and ORDER_NO in (SELECT ORDER_NO from IFSAPP.customer_order_line WHERE STATE <> 'Cancelled' and CATALOG_NO in ('1490-090','1490-070'))

Possibly I’m not familiar enough with the Cloud filter syntax, but I couldn't get any results from the CustOrdLines page using just the filter.

I also tried Query Designer,  I couldn’t get results with more than one part number.

Hi @ShawnBerk 

Thanks for the feedback.

The Query Designer will support IN clauses and nested filters in coming releases.

The reason to avoid SQL and PLSQL as configurations is to provide a smooth upgrade experience and help stay evergreen. When large blocks of code is required to do something the better approach would be to manage them in version control as source files.

I understand that there are trade-offs here, access to a full-blown language like SQL/PLSQL at runtime (as configurations) provides great flexibility but comes at a price during upgrades since its hard to do things like impact analysis.

Hi @durette 

We are using special oracle users IFSDBREADONLY and IFSMIG for read only access of oracle database.

https://docs.ifs.com/techdocs/22r1/030_administration/010_security/010_users/

Best regards,

Ravi

I understand that there are trade-offs here, access to a full-blown language like SQL/PLSQL at runtime (as configurations) provides great flexibility but comes at a price during upgrades since its hard to do things like impact analysis.

The problem is that the trade-off prohibits running the business daily on a software platform that seems to have ignored how customers actually use it. The inefficiencies that are currently in Cloud are still too vast for our users to accept the transition from V9 IEE.  It is very telling that there are still customers considering/planning on upgrading to V10 instead of Cloud after more than a year from release.  It feels like the the design trajectory was planned and implemented by people who had never run a business with ERP software.

We continue to wait for enhancements such as the ones you mention that should have been available from the first release.

@ShawnBerk , RnD has always been counting on the feedback we receive for numerous usage patterns by our valuable customer, while developing new platforms / features. As Rifki also mentioned (& you also may agree), having complex configurations specially with direct SQL, was one of the major barriers when it comes to upgrades, causing de-satisfaction among customers. The vision even from Darren & Christian is to have more greener upgrade process in IFS Cloud which benefits in most areas, setting up a win-win situation for both customers as well as IFS.

In order to avoid having complex configurations in direct SQL in different concept areas, in addition to Query Designer, you can use concepts such as BPA Workflows. For others, of course extend on inside would be the much better solution.
 

The vision even from Darren & Christian is to have more greener upgrade process in IFS Cloud which benefits in most areas, setting up a win-win situation for both customers as well as IFS.

I understand the vision, but from the very beginning of our experience (no Race environment available) through till now, the result at each turn seems to be to completely strip back the existing tools to make it easier on IFS for upgrades as they can ignore what customers might have implemented and say IFS is only responsible for the core.

As an example, the BPA tool has been sold (and purchased) as a solution to many of these issues, yet we have struggled to find an expert at using the tool that is available to demonstrate its use for our purposes. It seems like it is a necessary function to be implemented because the Aurena UI is so incredibly painful for heavy data entry.  Simple things that used to be done in IEE in minutes, are not even possible in Aurena. (find and replace a column value on an overview screen for 500 records).  Not possible, in fact, until 22R1, even viewing more than 24 records on the screen with a single search was impossible.  The solution now to do that type of update is the much longer route of creating a migration job, meaning the user can no longer manage data, only IT can.  These type of deprecations within 5 minutes of showing a experienced user their new world leave nothing but a bad view of their work experience to come.

But even when we get experienced enough using the BPA tool to replace some of the UI damage to keyboard operators, it will not replace the ability to perform a simple subquery search on any page to reference a value not available in that view.  Certainly not with a user creating such a query on the fly when required.  Which was actually the impetus of the original question, how do provide a safe, read-only path to accomplish some of these advanced queries that are needed on a daily basis.

The main outcome of the experience with Cloud so far is that IFS has reduced its footprint within the application by removing all of the tools that were deemed ‘troublesome’ then handed over a product that requires a significantly higher level of IT intervention and Partner support to return the product to something that is palatable for the end users to work with day in and day out.

We can see the intent of the vision, but thus far, the journey to get there has been not one of moving forward in leaps and bounds with a new product, it has been in fact a monumental effort to get back to somewhere near the efficiencies we had already achieved.  Currently, we have many concessions we will have to sell to our users because the simple fact is that their overall work in many areas will just become harder.  That is a hard sell to the organization at the user level and an even harder sell to the organization at the management level when coupled with the many surprise add ons that are required to complete the existing experience.

I’ve now high-jacked this thread so far away from the original question (sorry Kevin), that I’ll just end the thought there.  My experience still is not aligned with the vision intent at any point in this journey.