Purchase Order

Purchase Order's Database

UI Name

Database

Type

Remarks

P/O No

String

Avoid using the same P/O No to prevent overwrite.

E.g.: PO-123456

P/O Key

Number

E.g.: 123456

Last Modified

String

E.g.: 2024-12-19T08:57:37.557

Creditor

String

Creditor must match existing creditor code, and they must be active state.

E.g.: 400-C001

Name

String

E.g.: ABC SUPPLIER

Supplier DO No

String

E.g.: DO-654321

Address [Line 1]

String

E.g.: 123 INDUSTRIAL PARK

Address [Line 2]

String

E.g.: ZONE 4

Address [Line 3]

String

E.g.: CITY, STATE 12345

Address [Line 4]

String

E.g.: null

Date

String

E.g.: 2024-12-19T00:00:00

Branch

String

E.g.: BR001

Credit Term

String

E.g.: NET 30

Purchase Location

String

Must have existing Purchase Location in the accounting system.

E.g.: NY

Purchase Agent

String

E.g.: JOHN SMITH

Ship Via

String

E.g.: AIR

Ship Info

String

E.g.: EXPRESS

Total (ex)

Number

E.g.: 1000

Inclusive

String

E.g.: T

Taxable Amount

Number

E.g.: 100

Tax

Number

E.g.: 10

Currency

String

E.g.: USD

Rate

Number

E.g.: 1.0

Local Total

Number

E.g.: 1100

Total

Number

E.g.: 1100

Sequence

Number

E.g.: 1

Item Code

String

E.g.: ITEM-001

Description

String

E.g.: ITEM DESCRIPTION

Description 2

String

E.g.: ADDITIONAL INFO

Delivery Date

String

E.g.: 2024-12-20T00:00:00

Location

String

E.g.: WAREHOUSE 1

UOM

String

E.g.: PCS

Quantity

Number

Value must be number, and value cannot be negative.

E.g.: 10

Unit Price

Number

Value must be number, and value cannot be negative.

E.g.: 100

Discount

String

E.g.: 10%

C/N Amt.

String

E.g.: 50

Subtotal

Number

E.g.: 900

Subtotal (ex)

Number

E.g.: 800

Detail Tax

Number

E.g.: 80

Ref Document No

String

E.g.: DOC-001

UDF Description 2

String

E.g.: CUSTOM FIELD

Purchase Order's Sample Code

Remember to change to the correct field before proceeding to run ANY script.

This is a sample SQL, may not be as same as your database.

SELECT TOP (10)
    Doc AS Document,
    DocKey AS DocumentKey,
    LastModified,
    CreditorCode,
    CreditorName,
    InvAddr1,
    InvAddr2,
    InvAddr3,
    InvAddr4,
    DocDate,
    BranchCode,
    DisplayTerm,
    PurchaseLocation,
    PurchaseAgent,
    ShipVia,
    ShipInfo,
    TotalExTax,
    InclusiveTax,
    TaxableAmt,
    Tax,
    CurrencyCode,
    CurrencyRate,
    LocalNetTotal,
    NetTotal,
    Seq,
    ItemCode,
    Description,
    Desc2,
    DeliveryDate,
    Location,
    UOM,
    Qty,
    UnitPrice,
    Discount,
    SubTotal,
    SubTotalExTax,
    DtlTax,
    FromSODocList,
    FurtherDescription,
    LastModified
FROM PurchaseOrders
WHERE LastModified > "{LAST_MODIFIED_DATE_TIME}";

In order to sync, you must include LastModified in SELECT

"SQLSync": [
  {
    "name": "AutoCount to Inistate",
    "enabled": false,
    "connectionString": "Data Source={SERVER};Initial Catalog={DATABASE};User Id={DB_USERNAME};Password={DB_PASSWORD};",
    "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
    "sql": "SELECT AccNo, CompanyName, Address1, Address2, Address3, Address4, Phone1, DisplayTerm, EmailAddress, SalesAgent, LastModified \nFROM Debtor \nWHERE LastModified > @date",
    "lastSync": "{LAST-SYNC-DATE-TIME}",
    "start": null
  }
]

To find {fieldName}, you are required to have access to Alpha or use Network in Developer Tools [F12].

#debug('test', inputs)
def getFlatten(list, table):
    details = [dict(Line=y, Document=x) for x in list for y in x[table]]
    return details
    
moduleName = '{YOUR-MODULE-NAME}'
inistateKeyField = 'DocumentId'
keyField = 'Doc'
modifiedDateField = 'LastModified'
fields = [['DocumentId', 'Doc'],['LastModified', 'LastModified']]
inistateFields = []
tableField = 'Items'
tableColumns = ['Seq','ItemCode','Description','Desc2','DeliveryDate','Location','UOM','Qty','UnitPrice','Discount','SubTotal','SubTotalExTax','DtlTax','FromSODocList','FurtherDescription']

def createLine(x):
    item = JObject()
    main = JObject.FromObject(x[0])
    
    for field in main:
        if not tableColumns.Contains(field.Name):
            item[field.Name] = main[field.Name]
        
    item['items'] = JArray()
        
    for itemRow in x:
        rowJSON = JObject()
        for field in tableColumns:
            rowJSON[field] = itemRow[field]
        item['items'].Add(rowJSON)
        
    return item

def productAddToSync(product):
    if product is not None:
        form = Module('{YOUR-MODULE-NAME}').execute('Set To Sync', product)
        form['To Sync'] = True
        Module('{YOUR-MODULE-NAME}').submit(product, 'Set To Sync', form)
    
result = list(inputs["list"]).GroupBy(lambda x: x[keyField].ToString()).Select(lambda x: createLine(x)).ToList()

for item in result:
    entry = Module('{YOUR-MODULE-NAME}').find({ 'Dynamic.Extension.{fieldName}': item['Doc'] })
    # debug('test', item['Doc'])
    # debug('valid', entry['SyncDateTime'])
    # debug('valid time', item['LastModified'])
    form = Module('{YOUR-MODULE-NAME}').execute('sync', entry)
    form['{YOUR-CREDITOR}'] = item['CreditorCode']
    form['{YOUR-NAME}'] = item['CreditorName']
    form['{YOUR-ADDRESS1}'] = item['InvAddr1']
    form['{YOUR-ADDRESS2}'] = item['InvAddr2']
    form['{YOUR-ADDRESS3}'] = item['InvAddr3']
    form['{YOUR-ADDRESS4}'] = item['InvAddr4']
    form['{YOUR-P/O NO}'] = item['Doc']
    form['{YOUR-DOC-DATE}'] = item['DocDate']
    form['{YOUR-BRANCH}'] = item['BranchCode']
    form['{YOUR-CREDIT TERM}'] = item['DisplayTerm']
    form['{YOUR-PURCHASE LOCATION}'] = item['PurchaseLocation']
    form['{YOUR-PURCHASE AGENT}'] = item['PurchaseAgent']
    form['{YOUR-SHIP VIA}'] = item['ShipVia']
    form['{YOUR-SHIP INFO}'] = item['ShipInfo']
    
    form['{YOUR-SUBTOTAL (EX)}'] = item['TotalExTax']
    form['{YOUR-INCLUSIVE}'] = True if item['InclusiveTax'] != "F" else False
    form['{YOUR-TAXABLE AMOUNT}'] = item['TaxableAmt']
    form['{YOUR-TAX}'] = item['Tax']
    form['{YOUR-CURRENCY}'] = item['CurrencyCode']
    form['{YOUR-RATE}'] = item['CurrencyRate']
    form['{YOUR-LOCAL TOTAL}'] = item['LocalNetTotal']
    form['{YOUR-TOTAL}'] = item['NetTotal']
    
    # handle table
    lines = list(item['items']).ToList().OrderBy(lambda x: Convert.ToInt32(x['Seq'])).ToList()
    text = ''
    
    while len(form['Main']) > len(lines) :
        last = form['Main'].Last()
        productAddToSync(last['Item Code'])
        formLine = form.remove('Main',last)
        
    for line in lines:
        # create if none
        if len(form['Main']) <= lines.IndexOf(line):
            formLine = form.add('Main')
        else :
            formLine = form['Main'][lines.IndexOf(line)]
            
        # assign value to table row
        product = Module('{YOUR-MODULE-NAME}').find({ 'Dynamic.Extension.pIoowv': str(line['ItemCode'])  })
        if product is not None:
            if formLine['{YOUR-ITEM CODE}'].Id > 0 and formLine['{YOUR-ITEM CODE}'] != str(line['ItemCode']) and entry is not None :
                productAddToSync(formLine['{YOUR-ITEM CODE}'])
            formLine['{YOUR-ITEM CODE}'] = product
            
        formLine['{YOUR-DESCRIPTION}'] = line['Description']
        formLine['{YOUR-DESCRIPTION 2}'] = line['Desc2']
        formLine['{YOUR-DELIVERY DATE}'] = line['DeliveryDate']
        formLine['{YOUR-LOCATION}'] = line['Location']
        formLine['{YOUR-UOM}'] = line['UOM']
        formLine['{YOUR-QTY}'] = line['Qty']
        formLine['{YOUR-UNIT PRICE}'] = line['UnitPrice']
        formLine['{YOUR-DISCOUNT}'] = line['Discount']
        formLine['{YOUR-SUBTOTAL}'] = line['SubTotal']
        
        formLine['{YOUR-TOTAL (INC)}'] = Convert.ToInt32(line['SubTotalExTax'] if line['SubTotalExTax'] != None else 0) + Convert.ToInt32(line['DtlTax'] if line['DtlTax'] != None else 0) #no field in Autocount record total include tax, need add 2 value
        formLine['{YOUR-FROM S/O DOC. NO.}'] = line['FromSODocList']
        # formLine['Further Description'] = line['FurtherDescription'] #ignore first, take time to handle the image
        
    # debug('form', form)
    Module('{YOUR-MODULE-NAME}').submit(entry, form)
    
    # after = Module('Purchase Order').find({ 'Dynamic.Extension.zHLOEu': item['Doc'] })
    # if after is not None:
    #     for item in after['Main']:
    #         if item['Item Code'].Id > 0:
    #             form2 = Module('Product').execute('Update Stocks', item['Item Code'])
    #             result2 = Module('Product').submit(item['Item Code'], 'Update Stocks', form2)
debug('result', result)

PreviousDebtor NextGood Receive

Last updated 1 month ago

Purchase Order

Purchase Order's Database

UI Name

Database

Type

Remarks

P/O No

Doc

String

Avoid using the same P/O No to prevent overwrite.

E.g.: PO-123456

P/O Key

DocKey

Number

E.g.: 123456

Last Modified

LastModified

String

E.g.: 2024-12-19T08:57:37.557

Creditor

CreditorCode

String

Creditor must match existing creditor code, and they must be active state.

E.g.: 400-C001

Name

CreditorName

String

E.g.: ABC SUPPLIER

Supplier DO No

SupplierDONo

String

E.g.: DO-654321

Address [Line 1]

InvAddr1

String

E.g.: 123 INDUSTRIAL PARK

Address [Line 2]

InvAddr2

String

E.g.: ZONE 4

Address [Line 3]

InvAddr3

String

E.g.: CITY, STATE 12345

Address [Line 4]

InvAddr4

String

E.g.: null

Date

DocDate

String

E.g.: 2024-12-19T00:00:00

Branch

BranchCode

String

E.g.: BR001

Credit Term

DisplayTerm

String

E.g.: NET 30

Purchase Location

PurchaseLocation

String

Must have existing Purchase Location in the accounting system.

E.g.: NY

Purchase Agent

PurchaseAgent

String

E.g.: JOHN SMITH

Ship Via

ShipVia

String

E.g.: AIR

Ship Info

ShipInfo

String

E.g.: EXPRESS

Total (ex)

TotalExTax

Number

E.g.: 1000

Inclusive

InclusiveTax

String

E.g.: T

Taxable Amount

TaxableAmt

Number

E.g.: 100

Tax

Tax

Number

E.g.: 10

Currency

CurrencyCode

String

E.g.: USD

Rate

CurrencyRate

Number

E.g.: 1.0

Local Total

LocalNetTotal

Number

E.g.: 1100

Total

NetTotal

Number

E.g.: 1100

Sequence

Seq

Number

E.g.: 1

Item Code

ItemCode

String

E.g.: ITEM-001

Description

Description

String

E.g.: ITEM DESCRIPTION

Description 2

Desc2

String

E.g.: ADDITIONAL INFO

Delivery Date

DeliveryDate

String

E.g.: 2024-12-20T00:00:00

Location

Location

String

E.g.: WAREHOUSE 1

UOM

UOM

String

E.g.: PCS

Quantity

Qty

Number

Value must be number, and value cannot be negative.

E.g.: 10

Unit Price

UnitPrice

Number

Value must be number, and value cannot be negative.

E.g.: 100

Discount

Discount

String

E.g.: 10%

C/N Amt.

CNAmt

String

E.g.: 50

Subtotal

SubTotal

Number

E.g.: 900

Subtotal (ex)

SubTotalExTax

Number

E.g.: 800

Detail Tax

DtlTax

Number

E.g.: 80

Ref Document No

FromDocNo

String

E.g.: DOC-001

UDF Description 2

UDF_Desc2

String

E.g.: CUSTOM FIELD

Purchase Order's Sample Code

Remember to change to the correct field before proceeding to run ANY script.

This is a sample SQL, may not be as same as your database.

SELECT TOP (10)
    Doc AS Document,
    DocKey AS DocumentKey,
    LastModified,
    CreditorCode,
    CreditorName,
    InvAddr1,
    InvAddr2,
    InvAddr3,
    InvAddr4,
    DocDate,
    BranchCode,
    DisplayTerm,
    PurchaseLocation,
    PurchaseAgent,
    ShipVia,
    ShipInfo,
    TotalExTax,
    InclusiveTax,
    TaxableAmt,
    Tax,
    CurrencyCode,
    CurrencyRate,
    LocalNetTotal,
    NetTotal,
    Seq,
    ItemCode,
    Description,
    Desc2,
    DeliveryDate,
    Location,
    UOM,
    Qty,
    UnitPrice,
    Discount,
    SubTotal,
    SubTotalExTax,
    DtlTax,
    FromSODocList,
    FurtherDescription,
    LastModified
FROM PurchaseOrders
WHERE LastModified > "{LAST_MODIFIED_DATE_TIME}";

In order to sync, you must include LastModified in SELECT

"SQLSync": [
  {
    "name": "AutoCount to Inistate",
    "enabled": false,
    "connectionString": "Data Source={SERVER};Initial Catalog={DATABASE};User Id={DB_USERNAME};Password={DB_PASSWORD};",
    "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
    "sql": "SELECT AccNo, CompanyName, Address1, Address2, Address3, Address4, Phone1, DisplayTerm, EmailAddress, SalesAgent, LastModified \nFROM Debtor \nWHERE LastModified > @date",
    "lastSync": "{LAST-SYNC-DATE-TIME}",
    "start": null
  }
]

To find {fieldName}, you are required to have access to Alpha or use Network in Developer Tools [F12].

#debug('test', inputs)
def getFlatten(list, table):
    details = [dict(Line=y, Document=x) for x in list for y in x[table]]
    return details
    
moduleName = '{YOUR-MODULE-NAME}'
inistateKeyField = 'DocumentId'
keyField = 'Doc'
modifiedDateField = 'LastModified'
fields = [['DocumentId', 'Doc'],['LastModified', 'LastModified']]
inistateFields = []
tableField = 'Items'
tableColumns = ['Seq','ItemCode','Description','Desc2','DeliveryDate','Location','UOM','Qty','UnitPrice','Discount','SubTotal','SubTotalExTax','DtlTax','FromSODocList','FurtherDescription']

def createLine(x):
    item = JObject()
    main = JObject.FromObject(x[0])
    
    for field in main:
        if not tableColumns.Contains(field.Name):
            item[field.Name] = main[field.Name]
        
    item['items'] = JArray()
        
    for itemRow in x:
        rowJSON = JObject()
        for field in tableColumns:
            rowJSON[field] = itemRow[field]
        item['items'].Add(rowJSON)
        
    return item

def productAddToSync(product):
    if product is not None:
        form = Module('{YOUR-MODULE-NAME}').execute('Set To Sync', product)
        form['To Sync'] = True
        Module('{YOUR-MODULE-NAME}').submit(product, 'Set To Sync', form)
    
result = list(inputs["list"]).GroupBy(lambda x: x[keyField].ToString()).Select(lambda x: createLine(x)).ToList()

for item in result:
    entry = Module('{YOUR-MODULE-NAME}').find({ 'Dynamic.Extension.{fieldName}': item['Doc'] })
    # debug('test', item['Doc'])
    # debug('valid', entry['SyncDateTime'])
    # debug('valid time', item['LastModified'])
    form = Module('{YOUR-MODULE-NAME}').execute('sync', entry)
    form['{YOUR-CREDITOR}'] = item['CreditorCode']
    form['{YOUR-NAME}'] = item['CreditorName']
    form['{YOUR-ADDRESS1}'] = item['InvAddr1']
    form['{YOUR-ADDRESS2}'] = item['InvAddr2']
    form['{YOUR-ADDRESS3}'] = item['InvAddr3']
    form['{YOUR-ADDRESS4}'] = item['InvAddr4']
    form['{YOUR-P/O NO}'] = item['Doc']
    form['{YOUR-DOC-DATE}'] = item['DocDate']
    form['{YOUR-BRANCH}'] = item['BranchCode']
    form['{YOUR-CREDIT TERM}'] = item['DisplayTerm']
    form['{YOUR-PURCHASE LOCATION}'] = item['PurchaseLocation']
    form['{YOUR-PURCHASE AGENT}'] = item['PurchaseAgent']
    form['{YOUR-SHIP VIA}'] = item['ShipVia']
    form['{YOUR-SHIP INFO}'] = item['ShipInfo']
    
    form['{YOUR-SUBTOTAL (EX)}'] = item['TotalExTax']
    form['{YOUR-INCLUSIVE}'] = True if item['InclusiveTax'] != "F" else False
    form['{YOUR-TAXABLE AMOUNT}'] = item['TaxableAmt']
    form['{YOUR-TAX}'] = item['Tax']
    form['{YOUR-CURRENCY}'] = item['CurrencyCode']
    form['{YOUR-RATE}'] = item['CurrencyRate']
    form['{YOUR-LOCAL TOTAL}'] = item['LocalNetTotal']
    form['{YOUR-TOTAL}'] = item['NetTotal']
    
    # handle table
    lines = list(item['items']).ToList().OrderBy(lambda x: Convert.ToInt32(x['Seq'])).ToList()
    text = ''
    
    while len(form['Main']) > len(lines) :
        last = form['Main'].Last()
        productAddToSync(last['Item Code'])
        formLine = form.remove('Main',last)
        
    for line in lines:
        # create if none
        if len(form['Main']) <= lines.IndexOf(line):
            formLine = form.add('Main')
        else :
            formLine = form['Main'][lines.IndexOf(line)]
            
        # assign value to table row
        product = Module('{YOUR-MODULE-NAME}').find({ 'Dynamic.Extension.pIoowv': str(line['ItemCode'])  })
        if product is not None:
            if formLine['{YOUR-ITEM CODE}'].Id > 0 and formLine['{YOUR-ITEM CODE}'] != str(line['ItemCode']) and entry is not None :
                productAddToSync(formLine['{YOUR-ITEM CODE}'])
            formLine['{YOUR-ITEM CODE}'] = product
            
        formLine['{YOUR-DESCRIPTION}'] = line['Description']
        formLine['{YOUR-DESCRIPTION 2}'] = line['Desc2']
        formLine['{YOUR-DELIVERY DATE}'] = line['DeliveryDate']
        formLine['{YOUR-LOCATION}'] = line['Location']
        formLine['{YOUR-UOM}'] = line['UOM']
        formLine['{YOUR-QTY}'] = line['Qty']
        formLine['{YOUR-UNIT PRICE}'] = line['UnitPrice']
        formLine['{YOUR-DISCOUNT}'] = line['Discount']
        formLine['{YOUR-SUBTOTAL}'] = line['SubTotal']
        
        formLine['{YOUR-TOTAL (INC)}'] = Convert.ToInt32(line['SubTotalExTax'] if line['SubTotalExTax'] != None else 0) + Convert.ToInt32(line['DtlTax'] if line['DtlTax'] != None else 0) #no field in Autocount record total include tax, need add 2 value
        formLine['{YOUR-FROM S/O DOC. NO.}'] = line['FromSODocList']
        # formLine['Further Description'] = line['FurtherDescription'] #ignore first, take time to handle the image
        
    # debug('form', form)
    Module('{YOUR-MODULE-NAME}').submit(entry, form)
    
    # after = Module('Purchase Order').find({ 'Dynamic.Extension.zHLOEu': item['Doc'] })
    # if after is not None:
    #     for item in after['Main']:
    #         if item['Item Code'].Id > 0:
    #             form2 = Module('Product').execute('Update Stocks', item['Item Code'])
    #             result2 = Module('Product').submit(item['Item Code'], 'Update Stocks', form2)
debug('result', result)

PreviousDebtor NextGood Receive

Last updated 1 month ago