Update OEINVH shipping tracking number C# 2

[Adam007]

Hey All,

i have a issue when attempting to update the OEINVH table.
Im trying to locate the invoice base on the INVNUMBER field, but the record is never found.

Any help would be appreciated.
Here is my sample code;

xapiView OEINV1header = null;
xapiFields OEINV1headerFields = null;
xapiView OEINV1detail1 = null;
xapiFields OEINV1detail1Fields = null;
xapiView OEINV1detail2 = null;
xapiFields OEINV1detail2Fields = null;
xapiView OEINV1detail3 = null;
xapiFields OEINV1detail3Fields = null;
xapiView OEINV1detail4 = null;
xapiFields OEINV1detail4Fields = null;
xapiView OEINV1detail5 = null;
xapiFields OEINV1detail5Fields = null;
xapiView OEINV1detail6 = null;
xapiFields OEINV1detail6Fields = null;

try
{

    // Compose
    OEINV1header = OpenView("OE0420");
    OEINV1headerFields = OEINV1header.Fields;

    OEINV1detail1 = OpenView("OE0400");
    OEINV1detail1Fields = OEINV1detail1.Fields;

    OEINV1detail2 = OpenView("OE0720");
    OEINV1detail2Fields = OEINV1detail2.Fields;

    OEINV1detail3 = OpenView("OE0160");
    OEINV1detail3Fields = OEINV1detail3.Fields;

    OEINV1detail4 = OpenView("OE0660");
    OEINV1detail4Fields = OEINV1detail4.Fields;

    OEINV1detail5 = OpenView("OE0427");
    OEINV1detail5Fields = OEINV1detail5.Fields;

    OEINV1detail6 = OpenView("OE0422");
    OEINV1detail6Fields = OEINV1detail6.Fields;

    OEINV1header.Compose(new object[]{ OEINV1detail1, OEINV1detail4, OEINV1detail3, OEINV1detail2, OEINV1detail5, OEINV1detail6});

    foreach(string ordInv in invoices)
    {
        OEINV1header.Fields.Item("INVNUMBER").PutWithoutVerification(ordInv);
        OEINV1header.Read();

        if (Convert.ToBoolean(OEINV1header.Exists)) // make sure the invoice exists
        {
            OEINV1header.Fields.Item("SHIPTRACK").Value = newFlag;

            OEINV1header.Verify();
            OEINV1header.Process();
            OEINV1header.Update();
        }
        else
        {
            MessageBox.Show(string.Format("The invoice with the invoice number of {0} couldn't be found.", ordInv));
        }
        
    }

    return true;
}
catch
{
    string xapiErr = GetXAPIError();

    if (!string.IsNullOrEmpty(xapiErr))
        throw new Exception(xapiErr);
    else
        throw;
}
finally
{
    if (OEINV1header != null) { OEINV1header.Close(); OEINV1header = null; }
    if (OEINV1headerFields != null) { OEINV1headerFields = null; }
    if (OEINV1detail1 != null) { OEINV1detail1.Close(); OEINV1detail1 = null; }
    if (OEINV1detail1Fields != null) { OEINV1detail1Fields = null; }
    if (OEINV1detail2 != null) { OEINV1detail2.Close(); OEINV1detail2 = null; }
    if (OEINV1detail2Fields != null) { OEINV1detail2Fields = null; }
    if (OEINV1detail3 != null) { OEINV1detail3.Close(); OEINV1detail3 = null; }
    if (OEINV1detail3Fields != null) { OEINV1detail3Fields = null; }
    if (OEINV1detail4 != null) { OEINV1detail4.Close(); OEINV1detail4 = null; }
    if (OEINV1detail4Fields != null) { OEINV1detail4Fields = null; }
    if (OEINV1detail5 != null) { OEINV1detail5.Close(); OEINV1detail5 = null; }
    if (OEINV1detail5Fields != null) { OEINV1detail5Fields = null; }
    if (OEINV1detail6 != null) { OEINV1detail6.Close(); OEINV1detail6 = null; }
    if (OEINV1detail6Fields != null) { OEINV1detail6Fields = null; }
}

 

[Jay Converse]

Use .Browse and .Fetch.

 

[Adam007]

Hey tuba2007,

Thanks for the quick reply. Now I'm not entirely sure this is working properly here is what i changed;

foreach(string ordInv in invoices)
    {
        OEINV1header.Fields.Item("INVNUMBER").PutWithoutVerification(ordInv);
        OEINV1header.Browse("(INVNUMBER = '" + ordInv + "')", 1);
        if (Convert.ToBoolean(OEINV1header.Fetch())) // make sure the invoice exists
        {
            OEINV1header.Fields.Item("SHIPTRACK").Value = newFlag;

            OEINV1header.Verify();
            OEINV1header.Process();
            OEINV1header.Update();
        }
        else
        {
            MessageBox.Show(string.Format("The invoice with the invoice number of {0} couldn't be found.", ordInv));
        }
        
    }

When I run this code, the entire system just sits there and hangs.
If I watch the DB in profiler I can seen a ton of queries all executing but the only queries to the OEINVH table don't included my where clause.

Any ideas?

 

[Jay Converse]

Probably because the business logic is preventing you from updating. You need to use ADO to update existing invoice.

 

[ettienne]

This VBA works for me, you can translate to your flavor of C:

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Dim OEINV1header As AccpacCOMAPI.AccpacView
Dim OEINV1detail1 As AccpacCOMAPI.AccpacView
Dim OEINV1detail2 As AccpacCOMAPI.AccpacView
Dim OEINV1detail3 As AccpacCOMAPI.AccpacView
Dim OEINV1detail4 As AccpacCOMAPI.AccpacView
Dim OEINV1detail5 As AccpacCOMAPI.AccpacView
Dim OEINV1detail6 As AccpacCOMAPI.AccpacView
Dim OEINV1detail7 As AccpacCOMAPI.AccpacView
Dim OEINV1detail8 As AccpacCOMAPI.AccpacView
Dim OEINV1detail9 As AccpacCOMAPI.AccpacView
Dim OEINV1detail10 As AccpacCOMAPI.AccpacView

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
mDBLinkCmpRW.OpenView "OE0420", OEINV1header
mDBLinkCmpRW.OpenView "OE0400", OEINV1detail1
mDBLinkCmpRW.OpenView "OE0720", OEINV1detail2
mDBLinkCmpRW.OpenView "OE0160", OEINV1detail3
mDBLinkCmpRW.OpenView "OE0660", OEINV1detail4
mDBLinkCmpRW.OpenView "OE0427", OEINV1detail5
mDBLinkCmpRW.OpenView "OE0422", OEINV1detail6
mDBLinkCmpRW.OpenView "OE0401", OEINV1detail7
mDBLinkCmpRW.OpenView "OE0402", OEINV1detail8
mDBLinkCmpRW.OpenView "OE0404", OEINV1detail9
mDBLinkCmpRW.OpenView "OE0403", OEINV1detail10

OEINV1header.Compose Array(OEINV1detail1, OEINV1detail4, OEINV1detail3, OEINV1detail2, OEINV1detail5, OEINV1detail6)
OEINV1detail1.Compose Array(OEINV1header, OEINV1detail7, OEINV1detail10, OEINV1detail8)
OEINV1detail2.Compose Array(OEINV1header)
OEINV1detail3.Compose Array(OEINV1header)
OEINV1detail4.Compose Array(OEINV1header)
OEINV1detail5.Compose Array(OEINV1header)
OEINV1detail6.Compose Array(OEINV1header)
OEINV1detail7.Compose Array(OEINV1detail1)
OEINV1detail8.Compose Array(OEINV1detail1, OEINV1detail9)
OEINV1detail9.Compose Array(OEINV1detail8)
OEINV1detail10.Compose Array(OEINV1detail1)

OEINV1header.Order = 6 'Order by invoice number

OEINV1header.Init 'Reset view
OEINV1header.Fields("INVNUMBER").Value = "IN0000000000061"
OEINV1header.Read
OEINV1header.Fields("SHIPTRACK").PutWithoutVerification "Test123"
OEINV1header.Update

 

[ettienne]

Btw I tested this with an Accpac user (not ADMIN) and it is happy.

 

[Jay Converse]

Interesting, I've always assumed you couldn't update OEINVH without ADO.

I've got some code I need to update now...

 

[DjangMan]

The reason why your browse is taking so long is that your view isn't using a good index. Just before you .browse add in OEINV1header.order = 6 (which is by invoice number).

 

[Adam007]

Hey Guys,

Sorry for the long delay responding to this, i'm usually much better at getting back to the forums.

Anyways, here is the code that ended up working for my with the best performance;

YES i know these are the Credit Note views, i copied the wrong code but it should still be the same thing.

// Compose
OECRD1header = OpenView("OE0240");
OECRD1headerFields = OECRD1header.Fields;

OECRD1detail1 = OpenView("OE0220");
OECRD1detail1Fields = OECRD1detail1.Fields;

OECRD1detail2 = OpenView("OE0640");
OECRD1detail2Fields = OECRD1detail2.Fields;

OECRD1detail3 = OpenView("OE0140");
OECRD1detail3Fields = OECRD1detail3.Fields;

OECRD1detail4 = OpenView("OE0242");
OECRD1detail4Fields = OECRD1detail4.Fields;

OECRD1detail5 = OpenView("OE0221"); 
OECRD1detail5Fields = OECRD1detail5.Fields;

OECRD1detail6 = OpenView("OE0222");
OECRD1detail6Fields = OECRD1detail6.Fields;

OECRD1detail7 = OpenView("OE0224");
OECRD1detail7Fields = OECRD1detail7.Fields;

OECRD1detail8 = OpenView("OE0223");
OECRD1detail8Fields = OECRD1detail8.Fields;

OECRD1header.Compose(new object[] { OECRD1detail1, OECRD1detail2, OECRD1detail3, OECRD1detail4 });
OECRD1detail1.Compose(new object[] { OECRD1header, OECRD1detail5, OECRD1detail8, OECRD1detail6 });
OECRD1detail2.Compose(new object[] { OECRD1header });
OECRD1detail3.Compose(new object[] { OECRD1header });
OECRD1detail4.Compose(new object[] { OECRD1header });
OECRD1detail5.Compose(new object[] { OECRD1detail1 });
OECRD1detail6.Compose(new object[] { OECRD1detail1, OECRD1detail7 });
OECRD1detail7.Compose(new object[] { OECRD1detail6 });
OECRD1detail8.Compose(new object[] { OECRD1detail1 });

OECRD1headerFields.Item("DRIVENBYUI").Value = "1"; // Driven by UI
OECRD1detail1Fields.Item("DRIVENBYUI").Value = "1"; // Driven by UI
OECRD1header.Cancel();
OECRD1header.Init();

foreach (string crdInv in creditNotes)
{
    OECRD1header.Fields.Item("CRDUNIQ").Value = crdInv;
    OECRD1header.Order = 0;

    if (Convert.ToBoolean(OECRD1header.Read())) // make sure the invoice exists
    {
        OECRD1header.Fields.Item("SHIPTRACK").Value = newFlag;

        OECRD1header.Verify();
        OECRD1header.Process();
        OECRD1header.Update();
    }
    else
    {
        MessageBox.Show(string.Format("The credit note with the invoice number of {0} couldn't be found.", crdInv));
    }

}
return true;

Couple of things i'm not sure about though,

Do i really need to compose these if i'm not going to use them?

OECRD1detail1.Compose(new object[] { OECRD1header, OECRD1detail5, OECRD1detail8, OECRD1detail6 });
OECRD1detail2.Compose(new object[] { OECRD1header });
OECRD1detail3.Compose(new object[] { OECRD1header });
OECRD1detail4.Compose(new object[] { OECRD1header });
OECRD1detail5.Compose(new object[] { OECRD1detail1 });
OECRD1detail6.Compose(new object[] { OECRD1detail1, OECRD1detail7 });
OECRD1detail7.Compose(new object[] { OECRD1detail6 });
OECRD1detail8.Compose(new object[] { OECRD1detail1 });

Is this code really necessary?

OECRD1headerFields.Item("DRIVENBYUI").Value = "1"; // Driven by UI
OECRD1detail1Fields.Item("DRIVENBYUI").Value = "1"; // Driven by UI
OECRD1header.Cancel();

What is the difference between

    OECRD1header.Fields.Item("CRDUNIQ").Value = crdInv;
    OECRD1header.Order = 0;

    if (Convert.ToBoolean(OECRD1header.Read()))

and

    OECRD1header.Order = 0;
    OECRD1header.Browse("(CRDUNIQ = " + crdInv + ")");
    if (Convert.ToBoolean(OECRD1header.Fetch()))

performance wise?

Thanks again for everyones help and input, you guys are the best!

 

[ettienne]

Did you look at the VBA code I posted? I'm a minimalist and that is the least code you need to update the tracking #. I'm assuming from your original post that is what you want to achieve.

 

[ettienne]

To answer your questions:

Yes you need to compose the views, even if you think you are not going to use them.
You can drop the OECRD1headerFields = OECRD1header.Fields; lines.

You do not need the Driven by UI lines.

 

[Jay Converse]

1. You don't need to compose if you're not creating a new invoice, which you're not.
2. DRIVENBYUI is not required
3. If you don't set the .Order first, then your retrieval speed could be terrible. Note that you only have to set the .Order once when open the view, or when you're changing how you're retrieving:

0 - Invoice Uniquifier
INVUNIQ
1 - Order Number/Invoice Number
ORDNUMBER, INVNUMBER
2 - I/C Day End Trans. Number
DAYENDNUM
3 - Customer Number
CUSTOMER
4 - Customer/Invoice
CUSTOMER, INVNUMBER
5 - Reference/Invoice
REFERENCE, INVNUMBER
6 - Invoice Number
INVNUMBER
7 - Shipment Number/Invoice Number
SHINUMBER, INVNUMBER

 

[Adam007]

ettienne,

Thxs for your input, i didn't know i could drop the
OECRD1headerFields = OECRD1HeaderFields;
and i too am a minimalist, so you can bet thats coming out along with the DrivenBy lines.

Thanks again for all your help i really appreciate it.

 

[ettienne]

Difference between .Read and .Fetch performance wise is nothing, both use indexes to retrieve the records.

With .Read you have to use an index (set by .Order) and put the key values before you .Read.

With .Fetch you can filter records on non key fields.

My preference is to use .Read whenever I need to retrieve a specific record and I can use an index, otherwise I use .Fetch, but there is no reason not to use .Fetch to retrieve a single record.

 

[Adam007]

tuba2007,

thanks to you as well.
Both of you guys (yourself and ettienne) are great examples of educated people working in forums.

You both deserved the stars i gave you.

THANKS AGAIN YOU GUYS ROCK.