Fetching with field names

[amwalshjde]

I am interested in trying to have a more dynamic method of retrieving data from tables. I am writing a custom UBE, within this program I have a variable that hold the Data dictionary alias for a particular field (with or without the table column prefix I can get it either way). In this example, I will use category code 1 in the Address book file (ABAC01). I also have a customer number, 123456, what I want is the ABAC01 value for customer 123456.

How is this possible? Is is possible in ER code?

Thanks
Aaron

Xe 13.1 | Oracle | NT

 

[Zoltan_Gyimesi]

Hi Aaron,

Although your issue is not too clear for me but maybe my reply could be a bit help.

If you want to retrieve AC01 value for a particular F0101 record (AN8=123456) then you can use the FetchSingle statement in the Event Rules of an event.
You have to create a variable for both, AN8 and AC01 (based on these aliases!).
First assign your AB# to you AN8 based variable then issue the FetchSingle for F0101, selecting the primary key of F0101 and mapping AN8 based variable to the AN8 key field and the AC01 based variable to the AC01 field in the table to get back the value. Here is a simple example:

evt_AddresBookNo_AN8 = '123456'
F0101.FetchSingle
___evt_AddresBookNo_AN8 = TK Address Book Number
___evt_CatCode01_AC01 <- TK Category Code - Address Book 01

To read more about Table I/O statement, please check the appropriate chapters of the Development Tools documentation.

If you have further problems, let us know.

Regards,
Zoltán



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[amwalshjde]

Zoltan-

I wish were that simple. The field I want to work on can change, so one time it will be ABAC01 and the next it could be ABAC02, then ABAC09, etc. I need a programmatical way to start with a variable (that contains the data alias) and get to it's value. Here is a more detailed explaination. For pricing, you can specify which category codes define each group. There can be up to four category codes to define each customer group. I want to start with a customer number, use that to find out which group it is in (thats easy, just use the CPGP from the F03012). Then call the "F4092 Get Price Group Definition" business function by passing in that group name, and retrieving the four category code aliases. For example customer 123456 is in price group AWAL, which uses category codes 7,8,9,10. The business function returns AC07,AC08,AC09,AC10. I know which tables there are in and that does not change (F0101). The part that I am stuck on is getting the values for customer 123456 for those category codes. Knowing that the next customer is in group, WALA and that uses cat codes 11,12,13,14. IN that case I want those values not 7,8,9,10. The five values I need at the end of this process are customer number, and the udc value for the four cat codes


Does that make more sense?

Thanks for your help

 

[Frosty the Coder]

For THIS example, addr book & addr book cat codes, there are a couple
of bsfns that retrieve cat codes.

I am assuming that you are asking for more that just this example though,
and I'm not clear on what you are trying to accomplish

Are you looking to have one variable (ie szMY_VARIABLE_AC01) that you
want to use for diff table fetches? Or are you trying to NOT use fetch at
all?




Gene Piekarski, Jr

AS/400, B733, SP11.2, NT client
AS/400, B733, SP14, W2000 client
XE, SP13

 

[vbojan]

Hi Aaron,

Can you make 30 variables (AC01 – AC30) and fill all of them in F0101.FetchSingle. Also make 4 RV and in 5 “If statements” (there is no “Case statement” in ER) assign desired VA’s to RV’s. It is much easier than to make a BF for Fetch based on DD name.

Hope this helps,

Bojan.

 

[Zoltan_Gyimesi]

Hi Aaron,
Now your issue is clear for me and of course it makes more sense but I do not know any "flexible fetch" possibility using ER Designer. Maybe it could be feasible in C code.

I suppose, you know how you can make your code, fetching all Category Codes from F0101 and creating a bunch of IF statement to select the required 4. My only suggestion could be, place this logic into a custom BSFN, maybe you will want to use this logic elsewhere again. In this case you don't have to re-enter it with the lot of variables and IF statements.

Zoltán,
P.S.: Have you checked the original OW BSFNs, that is there any which make this logic. Maybe you can investigate some OW application for it where this logic is already used.

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[Frosty the Coder]

How about;

F(b):F4092 Get Price Group Definitions which would return
szMy PG Def_GPK1, _GPK2, GPK3, GPK4

F(b): Retreive F0101 Category Codes returning into
szCust_AC01, _AC02, _AC03, ....., _AC30

IF
_GPK1 = "AC01"
szWORK_AC01 = szCust_AC01
else
IF _GPK1 = "AC02"
szWORK_AC01 = szCust_AC02
else
IF _GPK1 = "AC03"
szWORK_AC01 = szCust_AC03
else.....

IF
_GPK2 = "AC01".......

IF
_GPK3 = "AC01"......

IF
_GPK4 = "AC01".....

GET UDC szWORK_AC01
GET UDC szWORK_AC02
GET UDC szWORK_AC03
GET UDC szWORK_AC04

granted, it's bulky, and not "pretty" but it may address the issue.




Gene Piekarski, Jr

AS/400, B733, SP11.2, NT client
AS/400, B733, SP14, W2000 client
XE, SP13

 

[WhippingBoy]

Aaron, you can't do what you want in ER.

Gene is right on the money as far as an ER solution goes. There's no getting around the verbose nature of ER in situations like this. Consider writing an NER that does the work and passes back the four values you want. "Write once. . use many" . . . . the programmers mantra (or maybe that's "steal the other guys code". . well, it's one of those two).

Now, if you want to get techie, take a close look at how the pricing stuff really works. . .B4200800 has the logic you're trying to duplicate.

The pricing stuff can be obscure sometimes, I know. I'm the guy who wrote it the first time around on the 400 and led the initial redesign in OW

Darren Ricciardi - OneWorld Whipping Boy

Looking for work in OR NEAR Amsterdam THE NETHERLANDS

 

[amwalshjde]

Darren-

I looked at B4200800, it would be a bit more useful if it had some (rather, any) useful documentation about how to use it. I screened throught the C code, and I was lost. I will probably take the route of creating a NER with lots of If statements. At the same time I will look a generic way to do this using C. Seeing how you are a pricing expert, here's one more question. I have been asked to develop a method that allows users to copy prices adjustments in batch. say for example there are two hundred item prices that apply to Customer group 1. Now there is a new customer group 2, that has no prices, they want to run this program and specify an amount/percent to change the price by and also what the new item/group and customer/group are.

The end result might be that now there are two hunderd item's prices for customer group 2, that are all 5% higher then the prices for group 1. This would save the user from all the data entry. that being said I am not a pricing expert so maybe I am going about this all wrong....

 

[WhippingBoy]

Yeah, that function is fairly criptic. The mega-IF statement NER would be far simpler and would accomplish the same thing.

Now your second part isn't actually that tough. You just need an understanding of the files involved. I think the manuals cover this pretty well, take a look at those.

The quick rundown on adjustments:
You've got 2 primary files to consider: F4072 and F4094. F4072 holds the adjustments themselves and F4094 holds the group information. ICID is the link between them.

So, this is a 'simple' solution for what you've described (a new GROUP). You can accomplish this programatically as you see fit, but these are the steps.

1. Create a new group definition(F4093).
2. Generate a new F4094 record for the new group and get a new ICID from next number.
3. Copy your existing adjustments from F4072 to new F4072s and replace the ICID with your NEW number from step 2. Viola, you have a new set of adjustments with the new group!
4. Run the Adjustment Update UBE to alter your new records by 5%.

That only covers the generation of a new GROUP, but you can see how easy it is. If you want to also generate a new ADJUSTMENT name you'll have to create an F4071 and fill in the defaults. Then drop that name into your new 4072s.

Pricing is actually very simplistic file-wise. Study the manuals and then scour the data for a bit. It will all make sense quickly.

I hope all that typing cleared things up. Let me know if I just confused you even more. . .

Darren Ricciardi - OneWorld Whipping Boy

Looking for work in OR NEAR Amsterdam THE NETHERLANDS