E9.2 How to get a query / watchlist working on a calculated grid column

[JohnDanter2]

Hi folks

I've been asked to make an E1 screen linking work orders on F4801 to open suggestions on F4611
Now I've developed the appl easy enough and I have 3 DD grid columns showing the count of the following:

1) Open 1st Picks: select count(*) from f4611 where r2dcto = work order type and r2doco = work order and r2phas = 1 and r2tyfl = ‘2’ and r2pstb <> '391'
2) Open 2nd Picks: select count(*) from f4611 where r2dcto = work order type and r2doco = work order and r2phas = 2 and r2tyfl = ‘2’ and r2pstb <> '391'
3) Total Picks: select count(*) from f4611 where r2dcto = work order type and r2doco = work order and r2phas = 1 and r2tyfl = ‘2’

Easy stuff, but I've also been asked to create alerts over these conditions so a query and a watchlist / notification is needed.
Thing is I can't add custom grid columns to a query. So I'm a little stuck unless I develop a BSVW over the 2 tables and do some kind distinct or hide/show grid row tricks to get.
Another idea I had was to use the grid record is fetched or write grid line before event to check these 3 values above and just hijack a field and populate them on the fly.
This way they are BSVW fields so I can use a query to find them (but I don't know if these queries check the DB or the GC for it's answer - I suspect the DB)
Another is to add a tag file to the F4801 and populate these 3 fields as the forms loads up and do it that way, but again they will be 0 on the way into the form

Any other ideas on how to solve this?

Thanks

John

 

[DaveWagoner]

ew ew ew. watching to see who might come up with something (or you yourself, more likely )

 

[DSauve]

You could create a pseudo-table that has the SQL counts built into it. So in your database, create a view named something like F594611WO, and pull in all the fields from the F4801 that you need, and include the 3 counts you mention as subqueries. Create a table definition in JDE with all the same naming conventions (e.g. table prefix and column names), and be sure to NOT GENERATE the table. Include this new table in your business view used in your application, and since the 3 counts are now considered table columns (even though behind the scenes they are subqueries), you can use them in a query/watchlist. Be sure to watch your indexes, too. You're talking about 3 subqueries for each F4801 record, so you'll want to be sure you're using indexes or else you may notice performance issues. If you're up to it, give it a whirl and share your results with the community.

 

[JohnDanter2]

Yeah thats crossed my mind, columns are actually SUM statements not DB values. I've done it before for finance stuff and do dropped views all the time for mapping to external tables in an E1 screen.
I may very try this.

I was also thinking of using the grid form events to filter out (hide or show) the records from the screen if the 3 counts are 0 and maybe then the query and subsequent would work WL (providing I dont' bypass form prcoessing)

 

[JohnDanter2]

Thanks Don, going ahead with your idea. My issue was how to link the 3 columns to F4801, but as you say it's a totally dummy view mimicking what I want on F4801 and the 3 fields.

Thanks

 

[JohnDanter2]

for anyone wondering what I did

I created the table as Don said using only the DD items I wanted off F4801 for my APPL and the 3 more columns added on to the end for the F4611 counts., build the table in E1, then run this to completely change it's behaviour

drop table F554611
/
drop view F554611
/
CREATE VIEW F554611 AS
SELECT
    WADOCO AS SSDOCO, WADCTO AS SSDCTO, WAAN8  AS SSAN8,  WAVR01 AS SSVR01,
    WAVR02 AS SSVR02, WAANSA AS SSANSA ,WAWR02 AS SSWR02, WAWR01 AS SSWR01,
    WAITM  AS SSITM,  WAURCD AS SSURCD, WADL01 AS SSDL01, WAMCU  AS SSMCU,
    WAMMCU AS SSMMCU, WATRDJ AS SSTRDJ, WADRQJ AS SSDRQJ, WASTRX AS SSSTRX,
    WAAITM AS SSAITM, WAUORG AS SSUORG, WARORN AS SSRORN, WARCTO AS SSRCTO,
    WALNID AS SSLNID, WAUOM  AS SSUOM,  WAQTYT AS SSQTYT,
    WASOQS AS SSSOQS, WASOCN AS SSSOCN, WASOBK AS SSSOBK, WARKCO AS SSRKCO,
    WAURRF AS SSURRF, WAURAB AS SSURAB, WAURAT AS SSURAT, WAURDT AS SSURDT,
    WAUSER AS SSUSER, WAPID  AS SSPID,  WAJOBN AS SSJOBN, WAUPMJ AS SSUPMJ,
    WATDAY AS SSTDAY, WASHTY AS SSSHTY, WATBM  AS SSTBM,
    WASRST AS SSSRST, WATYPS AS SSTYPS,
   
    (SELECT COUNT(*)
    FROM f4611
    WHERE
        r2dcto = w.WADCT AND
        r2doco = w.WADOCO AND
        r2phas = 1 AND
        r2tyfl = '2' AND
        r2pstb <> '391') AS SS34XC01,
    (SELECT COUNT(*)
    FROM f4611
    WHERE
        r2dcto = w.WADCT AND
        r2doco = w.WADOCO AND
        r2phas = 2 AND
        r2tyfl = '2' AND
        r2pstb <> '391') AS SS34XC02,
    (SELECT COUNT(*)
    FROM f4611
    WHERE
        r2dcto = w.WADCT AND
        r2doco = w.WADOCO AND
        r2phas = 1 AND
        r2tyfl = '2' ) AS SS34XC03
FROM
    F4801 w

E1 then thinks it has a table called F554611 which I can then also make an E1 BSVW over to use in an APPL, but it doesn't exist a real table (it's an SQL statement). It's now a view made up the above SQL that COUNTS the F4611 records.
Now I can make a watchlist and query over this 'table' and E1 is none the wiser

 

[DaveWagoner]

ahh yes the good ol' trick-jde-into-believing-that-a-view-was-a-table trick! good to see it still comes in handy in 9.x, I was using it on 7.3 as late as a little over a year ago