xAPI query 2

[Bluejay07]

Hello,

I am encountering an issue while programming a simple query. The odd part is that this query is based off of previous queries in the same project. For some reason, the query doesn't process correctly.

I have tried various methods of .read and .browse/fetch from simple to complex compositions. As soon as the .read or .fetch is reached, it reacts as though there is no information that matches. Here is some code examples:

   Set OEORDH = cfgSESS.OpenView("OE0520", "OE")
   With OEORDH
      .Fields("ORDNUMBER").Value = Trim$(l_strQuoteNum)

      If .Read Then
         l_strOrdUniq = Trim$(OEORDH.Fields("ORDUNIQ").Value)
      End If
   End With

   '// Set the views.
   Set OEORDH = cfgSESS.OpenView("OE0520", "OE")
   Set OEORDD = cfgSESS.OpenView("OE0500", "OE")
   Set OETERMO = cfgSESS.OpenView("OE0740", "OE")
   Set OECOINO = cfgSESS.OpenView("OE0180", "OE")
   Set OESELO = cfgSESS.OpenView("OE0680", "OE")
   Set OEORDQ = cfgSESS.OpenView("OE0526", "OE")
   Set OEORDHO = cfgSESS.OpenView("OE0522", "OE")
   Set OEORDDO = cfgSESS.OpenView("OE0501", "OE")
   Set OEORDDD = cfgSESS.OpenView("OE0502", "OE")
   Set OEORDDDS = cfgSESS.OpenView("OE0504", "OE")
   Set OEORDD0 = cfgSESS.OpenView("OE0503", "OE")
   
   OEORDH.Compose Array(OEORDD, OESELO, OECOINO, OETERMO, OEORDQ, OEORDHO)
   OEORDD.Compose Array(OEORDH, OEORDDO, OEORDD0, OEORDDD)
   OETERMO.Compose Array(OEORDH)
   OECOINO.Compose Array(OEORDH, OEORDD)
   OESELO.Compose Array(OEORDH, OEORDD)
   OEORDQ.Compose Array(OEORDH)
   OEORDHO.Compose Array(OEORDH)
   OEORDDO.Compose Array(OEORDD)
   OEORDDD.Compose Array(OEORDD, OEORDDDS)
   OEORDDDS.Compose Array(OEORDDD)
   OEORDD0.Compose Array(OEORDD)
   
   With OEORDH
      .Fields("ORDNUMBER").Value = Trim$(l_strQuoteNum)

      If .Read Then
         l_strOrdUniq = Trim$(OEORDH.Fields("ORDUNIQ").Value)
      End If
   End With

What am I missing? Any assistance would be greatly appreciated. In addition, please do not suggest using COMAPI as that is not an option for this program.

Thank you.



If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

Use .Browse and .Fetch.

 

[Bluejay07]

As mentioned above, I tried that as well and it produced the same result.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

Did you set .Order = 1?

 

[Jay Converse]

Wait, .Order should be 0 for ORDUNIQ.

Are you sure there's data to fetch?

 

[Bluejay07]

Well I changed it back to .browse and set the order to 0. I don't know if it works or not since it seemed to have locked up the program. I have now been waiting for more than 20 minutes so I don't know if it's searching very slowly or not at all.

Yes, there is definately data to be fetched.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

Here are the .Orders from the dictionary:

0 - Order Uniquifier Key
ORDUNIQ
1 - Order Number Key
ORDNUMBER
2 - Customer Number
CUSTOMER
3 - Type/Complete
TYPE, COMPLETE, ORDUNIQ
4 - Customer/Order
CUSTOMER, ORDNUMBER
5 - Reference/Order
REFERENCE, ORDNUMBER
6 - Customer/PO No.
CUSTOMER, PONUMBER
7 - Customer/OnHold/Type
CUSTOMER, ONHOLD, TYPE

If .Order is 0, you search on ORDUNIQ. If 1, ORDNUMBER, etc.

FWIW, I never use .Read if I'm not putting in the real key of the table, which in your case is ORDUNIQ.

 

[Bluejay07]

Thanks for that Tuba.

Here is my current code:

   With OEORDH
      .Init
      .Order = 0
      
      .Browse "ORDNUMBER =" & Trim$(l_strQuoteNum) & "", 1
      Do While .Fetch   '// Should only be one record but in case there is more.
         ' This does not get executed.
         l_strOrdUniq = Trim$(OEORDH.Fields("ORDUNIQ").Value)
      Loop
   End With

If I use .init then it processes with no result from the .fetch. If I omit the .init, the program hangs and freezes when executing .fetch.

I just opened OEORDH and verified the information is correct that I am referencing and it is the same.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

You only use .Init when you're adding a new order, not searching for one. Use .Cancel then .Browse

 

[Bluejay07]

After more testing, it appears that when the program was 'frozen', it was still processing. I added the .cancel, ran the program and let it process whatever it had to do.
After tracking the amount of time it took to process, it was 2.5 hours to process. That method did work as I was able to get information in the .fetch section but that wait time is not acceptable.

Looking at the database, there are 378130 rows in OEORDH. Any suggestions on a different method that may be faster? Would using CS0120 be beneficial in this case?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[DjangMan]

This code should work. I'm specifying the .Order to be by ORDNUMBER.

   Set OEORDH = cfgSESS.OpenView("OE0520", "OE")
   With OEORDH
      .Order = 1
      .Fields("ORDNUMBER").Value = Trim$(l_strQuoteNum)

      If .Read Then
         l_strOrdUniq = Trim$(OEORDH.Fields("ORDUNIQ").Value)
      End If
   End With

Now - one thing to be very very careful of is that if you change the .order you can get odd errors if you are posting transactions.

In cases like this I'll sometimes open up another OEORDH view just for locating a record and then I pass the orduniq to the set of views that I use for updating Accpac.


Browsing for an order number using index 0 will make Accpac start at the top of the table and read row-by-row.

Browsing for an order number using index 1 will be way faster - almost instantaneous.

Run rvSpy and dbSpy to see what the database is doing.

Using CS0120 can help because the database will figure out what index to use. ORDUNIQ=1234 will use the first index and be quick.

 

[Bluejay07]

Thank you Tuba and thank you DjangMan.
This code did work and it was almost instantaneous. Tuba, I guess your original assumption was correct with having .order = 1.
DjangMan, thank you as well for the advisory of posting transactions.

If at first you don't succeed, then sky diving wasn't meant for you!