how to exclude all pending invoices?

[pavelm]

Hello everyone,
some time ago I asked how to find all unpayed invoices without system check run on Accpac 5.4 (means, CS0120 view and SQL queries are not yet available).
tuba2007 gives me a perfect hint - I need to run query
SELECT * FROM APOBL WHERE SWPAID = 0
using AP0025-APOBL view. That's was good and worked some time, but now I need to exclude from the list all invoices with the "pending" state - means A/P invoice has been posted and corresponding A/P payment is created, but not posted.
Unfortunately, query above gives me the list of all A/P invoices, including those which already have corresponding payments (they are just not yet posted).

Now to exclude those pending payments, I have to run second query over AP0033(APTCP) Applied Payments view, checking if payment with appropriate document number (IDINVC) already exists, but I'm not sure it's the most effective way.
Is there any better way to do it?

Just in case, previous question discussion was here:

http://www.tek-tips.com/viewthread.cfm?qid=1578079

Thank you,
Pavel

 

[Jay Converse]

That's it. This is not a complicated process.

 

[pavelm]

Hello again,
I've got serious perfomance problem.
I run two nested cycles:
first over AP0025 (APOBL) view with SWPAID = 0 criteria.
Second cycle is nested - for all unpayed invoices I'm trying to find if they are already created but not posted: cycling over AP0033 (APTCP) with criteria:
BATCHTYPE="PY" AND ap0033.IDVEND=ap0025.IDVEND AND ap0033.IDINVC=ap0025.IDINVC
Here, if ap0033.Fetch() works well, I think payment already exists and it's not necessary to create it again - entry skipped.
The problem is that it's all incredible slow!
First cycle (if it's just empty fetch() and that's all) iterates over APOBL table with 30856 records within 28 seconds - it's more or less acceptable.

But second browse over ap0033 takes about 60 seconds to check/fetch every record! (ap0033 table has 18671 records now).
Strange, but if I remove IDVEND criteria from browse, it works much faster - just a few seconds.

I've tried to use Read() instead of Fetch() - something like this:
ap0033.BATCHTYPE.PutWithoutVerification("PY");
ap0033.IDVEND.PutWithoutVerification(ap0025.IDVEND.Value);
ap0033.IDINVC.PutWithoutVerification(ap0025.IDINVC.Value);
ap0033.Read();

but it doesn't work: probably because IDVEND/IDINVC just indexes, not the keys...

Do you have any idea how to speed-up a serach process?
I'm using Accpac 5.4 (i.e. there is no way to execute SQL queries with tCS102) and I have no direct access to Accpac DB - only Accpac API.

Here is the code:

AP0025_APOBL ap0025 = new AP0025_APOBL(login);
AP0033_APTCP ap0033 = new AP0033_APTCP(login);

ap0025.Browse("SWPAID = 0");
while (ap0025.Fetch())
{
ap0033.RecordClear();
ap0033.Browse("(BATCHTYPE=\"PY\") AND (IDVEND=\"" + ap0025.IDVEND.Value + "\") AND (IDINVC=\"" + ap0025.IDINVC.Value + "\")");

if (ap0033.Fetch()) // this works very-very slow!
{
//payment already exists, but not posted - should be in pending state...
continue;// skip it
}
else
{
add new payment for invoice entry
(here everything works well)
}
}

thank you for any idea in advance.

 

[pavelm]

also, note: ap0033 has no compositions with 0032, 0031, etc. - because I'm not doing any modifications - just querying and checking if record exists.

 

[Jay Converse]

ap0033.Order = 1;

 

[pavelm]

it seems, that it works much faster also if I made those ap0031-32-33 compositions...
How it could be?

thank you, tuba, trying it right now

 

[pavelm]

by the way, I'm using C# and there every Accpac view has a function

Browse(string search_criteria, bool ascending);

I always use
Browse (something, false)
and thought it's same that setting Order=1.
I was not right?

 

[ettienne]

Nope. Each view has a .Order that sets the index to use for the .Browse. If you do not set the index then it crunches through all records unless the primary key happens to be the correct one for the browse.

 

[ettienne]

The composition speeds things up because the filter applied to one view applies to all the others as well.

 

[pavelm]

strange, but why it affects all others? I actually don't need them...

Thanks for incredibly useful help...
Just to clarify:
Order selects the index to use? So, if view has many indexes, it can be order=2, order=3 and so on, depending on my search criteria?

And one more question - what is fastest command to use, just to get if at least any record is found after .Browse(): Fetch(), GoBottom() or something else?

Thank you guys, really... applications always was so slow... so simple way to optimize.

 

[ettienne]

Fetch and GoNext are the same. GoNext, GoPrevious, GoTop and GoBottom were added to allow flexible browsing, previously only Fetch was available. I guess the correct term is that Fetch is deprecated.
You don't want to use GoBottom unless you really have to retrieve the last record.

 

[Jay Converse]

.Order picks the index. Look here for details:

http://support.accpac.com/techsupport/aom/advantage.asp

For AP0033, this is what you have:

Index List - 3 Index(es)

0 - Batch Number / Entry Number
BATCHTYPE, CNTBTCH, CNTRMIT, CNTLINE
1 - Vendor Number / Document Number
IDVEND, IDINVC, CNTPAYM
2 - Batch Type / Batch Number
BATCHTYPE, CNTBTCH, CNTRMIT, IDINVC, CNTPAYM

 

[pavelm]

thank you very much,
everything works, rocks and just perfect.

appreciate your help. Thank you.