Data Array Reading Strange Data
  • JeffGrant
    Posts:32
    Joined:Thu Sep 20, 2018 3:38 pm
    Location:Sydney Australia
    Data Array Reading Strange Data

    by JeffGrant » Sun Feb 03, 2019 3:44 pm

    I use this script below to create a csv file to send data to a MySQL table. I find that sometimes I get all of the records in the dbf file and at other times I only get some of the records.

    Q1. Why does this happen?
    Q2. How can I ensure to get all of the records, all of the time?
    Q3. Is there any possibility that I could have two or more records in CUAUX000 for the same account code?




    ****R-CLOSE SCRIPT****
    * Export Fields from custrec.dbf & cuaux000.dbf

    *Script to export the customer record data as a CSV file.

    Declare PASSWORD Type String
    Declare PAY0 Type Numeric
    Declare PAY30 Type Numeric
    Declare Pay60 Type Numeric
    Declare Pay90 Type Numeric
    Declare InvBalance Type Numeric
    Declare aTran Type Array
    aTran := {}

    * Run upload batch file only if company is FirstCal

    If .NOT. CompanyName(2) = "FirstCall Mortuary Transfers"
    Goto SayByeBye
    Endif


    OpenTable("C:\CAPITAL\FIRSTCALL\CUSTREC")
    CUSTREC->(Find(CUSCODE))

    OpenTable("C:\CAPITAL\FIRSTCALL\CUAUX000")
    CUAUX000->(Find(AUXKEY))

    *Add Field Names for MySQL Import Mapping
    AADD(aTran, { "CusCode", "CusName", "CusHold", "CusBalance", "Password" } )

    :Loop
    If CUSTREC->(EOF())
    Goto WriteFile
    Endif

    *Sum Balance Due
    InvBalance := CUSTREC->Pay0 + CUSTREC->Pay30 + CUSTREC->Pay60 + CUSTREC->Pay90

    * Export Account Code, Account Name, Hold Status, Account Balance, Customer Password
    * find new/next AUXKEY in CUAUX000 for the next customer record

    CUAUX000->(Find(CUSTREC->CUSCODE))

    AADD(aTran, { CUSTREC->CUSCODE, CUSTREC->CUSNAME, CUSTREC->CUSHOLD, InvBalance, CUAUX000->PASSWORD } )

    * Skip to next Customer Record
    CUSTREC->(Skip())

    Goto Loop

    :WriteFile
    If Len(aTran) = 0
    Echo("Sorry, no transactions were found today.")
    Else
    If ExportASCII(aTran, PathProgram() + "upload\" + "cushold.csv", "DEL")
    Echo("File created: " + PathProgram() + "upload\" + "cushold.csv")
    Else
    Echo("Error creating: " + PathProgram() + "upload\" +"cushold.csv " + NTRIM(FError()))
    Endif
    Endif

    CloseTable("CUSTREC")
    CloseTable("CUAUX000")


    * Run upload batch file
    RunApplication ("\\kids-laptop\capital\upload\callmyftp.bat")


    :SayByeBye
  • COBS Tech Support
    Posts:683
    Joined:Fri Sep 09, 2005 8:23 am

    Re: Data Array Reading Strange Data

    by COBS Tech Support » Thu Feb 07, 2019 1:28 pm

    Your record positioning looks very ambiguous:

    Code: Select all

    OpenTable("C:\CAPITAL\FIRSTCALL\CUSTREC")
    CUSTREC->(Find(CUSCODE)) 
    If you don't care about the ordering you'd be better off doing this:

    Code: Select all

    OpenTable("C:\CAPITAL\FIRSTCALL\CUSTREC")
    CUSTREC->(GotoTop())

Who is online

Users browsing this forum: No registered users and 7 guests