Last Transaction Date on Cardex for Certain Doc Type

czarcasm

Active Member
I am hunting around for a way to grab the most recent transaction date for a certain doc type on the cardex file... The only way I can think of to do it right now is to perform a select -- fetch next with a do while. That would work, but would just destroy the processing. In SQL I would perform a select max(iltrdj) but that's about all I can think of right now. Is there a way to select max of a certain field given a doc type?
 

nkuebelbeck

VIP Member
how often would you need to do this?

What if you simple crushed the F4111 once to find the most recent, then just stored the most recent off in another table. The next time just do table io >last search date. just thinking out loud.
 

Larry_Jones

Legendary Poster
IF you can get away with it ... add a new index to F4111 based on Document Type and UKID DESC.
Then the First Select (Doc type) /Fetch Next against that Index will return the latest transaction.

There's a cost to adding a new index to this table - balance that against your need.
 

BOster

Legendary Poster
There is aggregate functionality in the JDEBASE APIs, unfortunately it doesn't work on date types (JDEDATE) - at least not in my TR. It only works with MATH_NUMERIC data types. However, since JDEDATEs are stored in the database as numeric data types, you could create v-table/faux jde table (whatever everyone calls it... a JDE table backed by a SQL view). The SQL view can alias TRDJ as a MATH_NUMERIC data type, something like GNUM0. Then you can use JDEBASE aggregate functionality to get the max value. It will be returned as a number so you will then have to convert the MATH_NUMERIC return value to a JDEDATE but at least you can effectively do "select max(trdj) where dcto = 'XX'..." in JDE and make the DB do all the work instead of JDE iterating over a billion records or doing something like create a select statement that returns a billion records in descending order by date just so you can read the first record.

Edit 1:
Reading over this, it occurred to me that if your WHERE requirements are as simple as doc type your virtual table/v-table/faux table/sql view could be as simple as

select XXDCTO, max(XXTRDJ) as XXTRDJ
from F59xxxxx
group by XXDCTO

Then you can do simple table I/O in JDE to read the record for a given doc type.
 
Last edited:
Discover how to build no-code data integrations and business process automations.

czarcasm

Active Member
@nkuebelbeck - Thought about that but this place hasn't archived their data since 02.... that file would be atrocious. (not that their cardex isn't already...)

@larry_jones - genius. I like it. I think I sneak away with something just like this. Thank you! I am going to see if I can sneak away without adding an index but I believe they already have an alternative index on the cardex that should provide what I need.

@BOster - I completely agree with what you wrote. Since this whole ordeal is a temp solution till we launch bar coding in the next few months (and archive data) I do not want to put that in place and then rip it out. It would be the least taxing way to go though, for sure.
 
Top