Set user selection for a list of values

cooljde

Member
Hi JDE Gurus,

This is my first post.

I am currently working on a requirement where I have to set the user selection for a UBE from a list of values in UDC . The UDC is dynamic and when users add values to te UDC, the values should get included in the data selection. I am validating the UDC values against one field in the source table. Is it possible to validate the values as a list of values or should they be individual OR statements?

For e.g.
Using set user selection, is it possible for me to set the data selection as follows

BC Doc Type is equal to 'SO,SD,SB'

(OR) is it possible to do only as follows

BC Doc Type is equal to 'SO' or
BC Doc Type is equal to 'SD' or
BC Doc Type is equal to 'SB'

Will there be any performance impact due to this difference? Any pointers on this would be very helpful.

Thanks.
-----------------------------------------------------
OneWorld Xe
 
Try This:

-------------------------------------------
Fetch F0005 (SY, RT)
For Each F0005.KY

FirstPass = 1
While File IO Success

If FirstPass = 1
Set Selection <F4211.DCTO = F0005.KY> AND
Else
Set Selection <F4211.DCTO = F0005.KY> OR
End If

<OtherFilter1> AND
<OtherFilter2> AND
<OtherFilter3> AND

FirstPass = 0

End While
-------------------------------------------

This is how the query should form:

WHERE
<External Selection>
AND
((F4211.DCTO = SO AND <OtherFilter 1,2,3>)
OR
(F4211.DCTO = SD AND <OtherFilter 1,2,3>)
OR
(F4211.DCTO = SB AND <OtherFilter 1,2,3>))

Turn on your debug log and make sure the query is formed right.
----------------------------------------------------

Get back if you did not understand.
Hope this was helpful…
 
Wait till someone comes up with a requirement for multiple columns to be filtered on a list. My above code can handle only one column, Documents type in this case.

Imagine if u want to filter object account (F0911.OBJ = 322000, 313000, 321000) and sub ledger types (F0911.SBLT = W, O) from a list, like this.

WHERE
<External Selection>
AND
(
F0911.GLOBJ = '322000' AND F0911.GLSBLT = 'W' AND <OTHERFILTER 1,2,3>
OR
F0911.GLOBJ = '322000' AND F0911.GLSBLT = 'O' AND <OTHERFILTER 1,2,3>
OR
F0911.GLOBJ = '313000' AND F0911.GLSBLT = 'W' AND <OTHERFILTER 1,2,3>
OR
F0911.GLOBJ = '313000' AND F0911.GLSBLT = 'O' AND <OTHERFILTER 1,2,3>
OR
F0911.GLOBJ = '321000' AND F0911.GLSBLT = 'W' AND <OTHERFILTER 1,2,3>
OR
F0911.GLOBJ = '321000' AND F0911.GLSBLT = 'O' AND <OTHERFILTER 1,2,3>
)

The above code will get complex with nested whiles.
 
true (your remark about the code getting complex that is; haven't checked the correctness of your example - it's way too early in the morning. need some more coffee first
cool.gif
)
 
Hi cooljde,

First of all, you are welcome aboard of JDEList.

This is just an addition to jdecoder's solution.

If you fetch F0005.KY for the list of values, then left trim the spaces before you use it in the SetUserSelection systemfunction, because the values in F0005 are left padded with space to 10 chars length.

Regards,

Zoltán
 
I don't know how much you can mod your UBE, but this is how I would do this.

1. Create a new table with two fields, one field is a GUID (globally unique identifier) or alias EDBT and the other field would be the same type as the field that you are trying to do data selection on, both fields in the primary key.

2. Change the BV for your UBE to have an inner join, joining on the field that you were wanting to do data selection on to your new table.

3. In the ER Code of you report call a function to return a globally unique identifier or if you used EDBT call EDBTGetNextNumber.

4. In the init section of your UBE, loop the UDC values and insert to the table using the GUID or EDBT and the UDC value.

5. Set data selection against the GUID or EDBT with the unique value from the BSFN call.

6. After the report finishes, delete the values you inserted into the table using the GUID or EDBT.
 
JDECoder,

Sometimes it is easier to allow the data selection a wide birth - and limit the processing within the code.

For example - Data select on OBJ.SUB:
3001.100 - 3001.299
3002.250 - 3003.175

Imagine the data selection that would have to occur to capture the oddball ranges for the above OBJ.SUB combos? I would not want to be the owner of the DS, that is for sure.

Now, if you select only OBJ 3001-3002 and within the Do section, you create logic that stops processing for anything outside the above obj.sub ranges - the code is very short... In effect - this is how the Financial Business Functions work (if the record is not within the ranges plugged into the function - they are ignored).

Sacrificing sufficient IO is not so much an issue - since the stop in processing takes place at the top of the Do Section.

The downside (yes, it is huge) - if the data selection needs to be changed - the code needs to be adjusted, also. However, in most cases - the internal logic can be somewhat controlled through processing options (planning a head is always a GREAT idea).

Kind of use the same logic as the financial functions do (huge amount of data selected, omit records during processing and get the counts you want as output)

(db)
 
Imagine writing reports were you want revenue on SO, WO, Contracts, etc... And COGS, Labor etc… that’s hardly 4 -5 object accounts which are dynamic. For that if you scan the whole range of revenue and expense I don’t think it’s a good idea. And your example considers a range; there are requirements which need data to be filtered on specific values/List. Look at the topic on the post for an instance.
And as far as performance is considered we are hitting the same indexes or I am hitting more optimum than you, OBJ and SUB and retrieve only specific data for processing.
Let's have a poll.

VOTE HERE
 
Actually, in Financial Reporting - the majority of the data selected is omitted at the Column level. You could select tens thousands of rows and only a handful will be incepted by the individual columns.

We've created a FASTR Conversion tool - that works explicitly on this process. The Data Selection is fairly open - the 'stop processing' occurs based on a a function that is very similar to a Financial function... It works the same way that FASTRs did in World (very little initial data selection and all the omission done at the row/column/function level

I didn't ask for a vote (or a challenge) - I did offer another means to do use or omit records based on internal code (instead of Data Selection) with the use of the Stop Processing system function. As you have pointed out - regarding Data Selection and the inability to DS based on Obj.Sub... to Obj.Sub... can be nuts.

Trying to maintain complex DS at the version level can be just a difficult as tweaking code in a Version to fit individual reports.

In my case, though, simplifying the DS will (will - I've tested) make the data selection faster. The downside is that each record has to be validated at the start of the Do Section and either processed or omitted (just like all the financial functions work).

Complex DS vs Internal Ommission - not really worth voting on, but a necessity worth knowing about.

(db)
 
The way to do it is to select the records from F0005 by SY and RT. Read the F0005 records in a loop. For every successful read move the KY into a variable and select on the variable. As the select statement is part of the loop it will also be executed for every read. I have used it many times and it works.
Jacob
 
Why don't you just link the table to the UDC and select based on the UDC Values? Add the F0005 to the BSVW and you can do the data select to include whatever UDC Values are desired? It could resolve some other UDC question/lookup at the same time.

There would be no external table, no internal log and no 'stop processing'...

Maybe JDECoder needs to adjust his vote to include 'adding the F0005 to the BSVW to allow the dynamic entry of UDCs to the table'

??

(db)
 
I'm a BA in the land of Developers, but I have a couple of questions:

Are you doing this through Table Conversion, or is your file defined in JDE?

--If it's defined in JDE, is it a custom table or a standard JDE file?

--Are you doing any processing on the records that don't fit your criteria?

----If it's a custom JDE table and you don't need to process the rejects, then I'd suggest associating the field in the Data Dictionary with an appropriate UDC.

Then you can leave the selections in the Data Selection section. That way if a new UDC value is added then a BA can just change the Data Selection on the Version and a Deveoper doesn't have to get involved and can spend their valuable time on more technical stuff.

Just MHO.
laugh.gif
 
Dan,

As a general coment, using a join with the F0005 to aid in data selection may not work as in most cases the F0005.DRKY format (text value with possible space padding on the left) would be different to the target table/value format.

I believe that your "stop processing" technique has it's uses. I had not heard nor thought of it before. Thanks, for posting it. Now, when there is a need, I will definitely be considering it.
 
Oh, but Peter - you of little faith....

I was waiting for someone to question that (the padding and such within F0005)...

My favorite subject in E1 (Virtual Tables) takes care of that issue, whenever the padding doesn't play nicely)... In the SQL of the SQL-View, you strip the padding and set the equals. I know it works, because I had to do it for a client (it was in their definition, not mine). I should have the sql, somewhere - I'll post when I get access to the HOMO server this weekend.

I know - considerably more overhead than the original requirement (for someone that hasn't dabbled a lot).

(db)
 
Dan,

Yes, of course that would work. But the name of the virtual table cannot be called F0005. Thus, to be pedantic, you can't add the F0005 to the BSVW.

So it is not faith I lack, but a keen understanding of the mind of a certain Daniel Bohner ... not that such an understanding would be something that I would ... pursue ...
grin.gif
 
Let’s say – that for some stupid reason (like proof of concept), you wanted to link the F0101.ABAT1 field to its matching F0005.DRKY? How would you do that?


If you had a simple BSVW that linked the F0101.ABAT1 field to the F0005.DRKY – chances are, the only hits you would bring back are those that are <blank>. The SQL generated from the application/BSVW would look similar to:
SELECT ABAN8, ABALPH, ABAT1, DRDL01 FROM TESTDTA/F0101, TESTCTL/F0005
WHERE DRSY='00' AND DRRT='ST' AND ABAT1=DRKY

/* notice – you have to include the DRSY and DRRT or who knows what you’d get */

Now, if you stripped the <blank>s from the ABAT1 and the DRKY – and re-run, you get something more accurate:
SELECT ABAN8, ABALPH, ABAT1, DRDL01 FROM TESTDTA/F0101, TESTCTL/F0005
WHERE DRSY='00' AND DRRT='ST' AND trim(ABAT1)=trim(DRKY)

Now create an SQL View over the previous SQL Statement:
Create View TESTDTA/F5501015 as (
SELECT ABAN8 DBAN8, ABALPH DBALPH, ABAT1 DBATI, DRDL01 DBDL01
FROM TESTDTA/F0101, TESTCTL/F0005
WHERE DRSY='00' AND DRRT='ST' AND trim(ABAT1)=trim(DRKY))

/* notice – I renamed the fields so they would all have the same prefix */

You can validate that the view ‘worked’ by doing:
SELECT *FROM TESTDTA/F5501015


* Within E1 – create a table definition with:
• File name – F5501015 (or whatever was in the SQL View)
• Column Prefix – SD
• Columns (in same order as select statement)
o AN8
o ALPH
o AT1
o DL01
• You can pick a primary key – but it is not necessary
• DO NOT GENERATE THE TABLE (the SQL View would be replaced)

Within E1 – Create a Business View over all columns of the F5501015 (V5501015)

Now you can create applications and reports that do not require additional logic to pull in the description from the F0005

NOTE – there are at least two flaws/compromises that need to be understood in this example:
1. This example crosses libraries – do not include SQL Views that cross libraries in your backup/restore policies. They can be recreated at any time. HOWEVER, if you back them up and need to recover one library or the other – the backup may fail when either library/file is missing.
2. In this example – the SQL View / Virtual Table ‘should not be updated’. Do not create any applications/reports that would update the Virtual Table over this example.

Since this is only for validation – some of you might want to know how to get rid of the SQL View:
DROP VIEW TESTDTA/F5501015


regards

(db)
 
Two questions:

would it be ...AND trim(ABAT1)=trim....
or ...AND trim(DBAT1)=trim....

And does the DB stand for ummmmm Daniel Bohner! NO! not self promotion! Data base Yeah, that's the ticket!

jk
Ben again,
 
Ben,

I cut/paste the SQL Statments from the Aqua tool - pretty much untouched (I found a rename with DBAAT1 instead of DBAT1 and corrected that on the site). Other than that - the scripts were run as in the example.

Within the where clause, the statement only know about the actual column names within the defined table. You have to trim(ABAT1) not the rename'd field to trim(DBAT1)....

Since I couldn't embed (db) in the column names, I relinquished to settle for BSP (blatant self promotion).... I guess I got caught <darn>

(db)
 

Similar threads

E9.1 Dummy Data Selection Fields
Replies
5
Views
475
FrankCLT
Vidushi
E9.2 Set selection append flag not working on a Find button on a subform
Replies
7
Views
836
BOster
BOster
E9.2 Logic Extension - {28=DD validation failed for value null}
Replies
4
Views
571
Kevin Long
Kevin Long
alfredorz
E9.2 IBM Db2 exception value out of range
Replies
4
Views
365
alfredorz
alfredorz
E9.2 The Adventures of a JDE 1099 Text File
Replies
2
Views
674
Kim Schmidt
Back
Top