Searching F4211 orders by F4006 Address

[johndanter]

Hi folks,

I have a custom APPL Find Browse over V4211L which allows users rapid access to SO lines and gives them the ability to update and select on DOCO/lines/shpns they want. (updates occur via Row Exits)

I am now being asked to allow the users to select orders based on F4006 addresses (we are not yet populating ZON STOP fully)
How can I do this? Would I have to make a new BSVW over F4006 and F4211?

Or can you think of another way?

Thanks

John

 

[Larry_Jones]

John,

one issue I would have attempting to do this is that in our environment only a subset of orders have entries (override addresses) in F4006. If I joined F4006 to F4211 I'd have a lot of null values. And we know how QBE doesn't like null values.

 

[johndanter]

Hi Larry,

Exactly. I was thinking an outer join, but yeah you're right even then it may get messed up.
I may create a dummy view and try it in ObjectBrowser first

 

[johndanter]

What am I finding is a left outer join on F4211 F4006 (* =) is working but.....

If I select on F4006.COUN for example, I still get records off F4211 that have no F4006 but I will find F4006 that match.

So I may have to add ER logic inside the Grid Record is Fetched to check on QBE not being blank and then ask is the matching F4006 blank. If so hide the grid record

 

[BOster]

Try a SQL 92 join. The older left outer join automatically adds "... OR IS NULL" to your WHERE clause, I believe the SQL 92 join does not. To validate the difference and turn on debug log and look at the generated SQL statement.

 

[johndanter]

You, my friend, are a shiny golden star

Working like a charm Thanks

 

[Larry_Jones]

If you want to include ALL orders with customer addresses regardless of whether there is a override (F4006 entry) or not consider going the virtual table route and create a database view that mimics a custom JDE table.
The database view could join Address Book (F0116) Addresses with any overrides from F4006 to F4211.

Just a thought.

 

[BOster]

Honestly, what Larry is proposing is most likely what your end users actually want. They just want to search for an order based on an address, they are not going to care whether that address is in F4006 or F0116 (and if its NOT in F4006 its not going to find the order at all).

 

[johndanter]

Sounds cool, but thankfully they want to look at F4006 overrides only