Alpha Video Training
Page 3 of 3 FirstFirst 123
Results 61 to 73 of 73

Thread: Importing data an auto-increment field

  1. #61
    Member Cristiano's Avatar
    Real Name
    Cristiano
    Join Date
    Jan 2007
    Location
    Venice (Italy)
    Posts
    384

    Default Re: Importing data an auto-increment field

    I'm sorry ,

    have the batteries of the keyboard down of her capacity .

    How do I create a dummy record?
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  2. #62
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Importing data an auto-increment field

    Here is the database
    I stripped all but what you need. I have run tested several times, and I have reversed it all back to start point now.
    Step1 . There is already existing the tmp order file as you sent. It has not been updated yet so you can test with it now. Or create a new one. Must be the same format (as is the ORDERFILE). Look at the tables if you like.
    The invoice number will start at 1. The INVOICE table and ORDERFILE table are empty right now.
    Step 2 the code below, cut and past into a script and play that script. It should add order numbers to INVOICE and then MOVE the TMP orders to ORDERFILE
    Step 3 look at a form INVOICEFORM, a simple default form to check the result, use it to step through invoices to see the orders attached to each.
    Since the TMP order is empty you can next import the next and follow the procedure from step 1.
    The ORDERFILE will accumulate - up to you to handle invoice and whatever happens thereafter.
    Attached Files Attached Files

  3. #63
    Member Cristiano's Avatar
    Real Name
    Cristiano
    Join Date
    Jan 2007
    Location
    Venice (Italy)
    Posts
    384

    Default Re: Importing data an auto-increment field

    Quote Originally Posted by Ray in Capetown View Post
    Here is the database
    I stripped all but what you need. I have run tested several times, and I have reversed it all back to start point now.
    Step1 . There is already existing the tmp order file as you sent. It has not been updated yet so you can test with it now. Or create a new one. Must be the same format (as is the ORDERFILE). Look at the tables if you like.
    The invoice number will start at 1. The INVOICE table and ORDERFILE table are empty right now.
    Step 2 the code below, cut and past into a script and play that script. It should add order numbers to INVOICE and then MOVE the TMP orders to ORDERFILE
    Step 3 look at a form INVOICEFORM, a simple default form to check the result, use it to step through invoices to see the orders attached to each.
    Since the TMP order is empty you can next import the next and follow the procedure from step 1.
    The ORDERFILE will accumulate - up to you to handle invoice and whatever happens thereafter.

    Ok Thank you Ray,

    I test and then I let you know
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  4. #64
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Importing data an auto-increment field

    No dummy records if you use the file as sent - it is set to start at 1, as I described earlier.
    Here is the code you need to run to add the Invoice number/s linked to the orderID in orderfile and clear the tmp order table
    Code:
    imf=table.open("tbl_temp_orders")
    inv=table.open("invoice")
    inv.index_primary_put("orderid")
    imf.fetch_first()
    while .not. imf.fetch_eof()
    	if inv.fetch_find(imf.order)<1
    		inv.enter_begin(.t.)
    		inv.ORDER= imf.ORDER 
    		inv.enter_end(.t.)
    	end if
    	imf.fetch_next()
    end while
    imf.Move_Records_To("orderfile")
    Last edited by Ray in Capetown; 01-13-2015 at 12:43 PM. Reason: best to add imf.fetch_first() as left out first

  5. #65
    Member Cristiano's Avatar
    Real Name
    Cristiano
    Join Date
    Jan 2007
    Location
    Venice (Italy)
    Posts
    384

    Default Re: Importing data an auto-increment field

    Quote Originally Posted by Ray in Capetown View Post
    No dummy records if you use the file as sent - it is set to start at 1, as I described earlier.
    Here is the code you need to run to add the Invoice number/s linked to the orderID in orderfile and clear the tmp order table
    Code:
    imf=table.open("tbl_temp_orders")
    inv=table.open("invoice")
    inv.index_primary_put("orderid")
    while .not. imf.fetch_eof()
    	if inv.fetch_find(imf.order)<1
    		inv.enter_begin(.t.)
    		inv.ORDER= imf.ORDER 
    		inv.enter_end(.t.)
    	end if
    	imf.fetch_next()
    end while
    imf.Move_Records_To("orderfile")
    Ok Ray,

    I'm sorry but I do not understand... I'm a bit confused ...

    Iin effect I gave for granted (and perhaps wrongly) a thing that only now I understand that it was not at all obvious.
    Let me explain better:


    In this project there is not a form to enter data . Is the Excel file that populate all the tables.


    I wrote a code thatl send you ( so you can understand how the code work)

    This code import the Excel file that compiles all the tables and then the user print invoices. So the system is (or should be) Automatic


    'Date Created: 10-Jan-2015 03:17:40 PM
    'Last Updated: 13-Jan-2015 05:24:35 PM
    'Created By : Cristiano
    'Updated By : Cristiano

    import_destination_table = filename_decode("tbl_temp_orders.dbf")
    temporary_import_table = file.temp_path_get()+ "tbl_temp_orders_temp_table.dbf"
    import_destination_table = "tbl_temp_orders"
    import_filename = filename_decode( ui_get_file("File to import", "Excel(*.xlsx)", "","X"))
    if file.exists(import_filename) = .f. then
    ui_msg_box("Errore","Nessun file trovato : '"+import_filename+"'.",ui_stop_symbol)
    end
    end if


    ON ERROR GOTO ERROR1001201520165984


    import.type = 3
    import.names = "use"
    import.file = import_filename
    import.db = temporary_import_table
    import.skip_lines = 0
    import.skip_bytes = 0
    import.field_sep = ""
    import.record_sep = ""
    import.text_qualifier = ""
    import.record_len = 0
    import.rem_blanks = .F.
    import.escape_newlines = .F.
    import.fields = 17
    import.field1 = "orderid,n,0,7,0"
    import.field2 = "name,c,1,38,0"
    import.field3 = "phone,n,2,19,0"
    import.field4 = "adress,c,3,51,0"
    import.field5 = "city,c,4,27,0"
    import.field6 = "postal_code,n,5,5,0"
    import.field7 = "country,c,6,2,0"
    import.field8 = "e_mail,c,7,22,0"
    import.field9 = "reference,c,8,15,0"
    import.field10 = "num_item,n,9,1,0"
    import.field11 = "subtotal,n,10,9,2"
    import.field12 = "currency,c,11,3,0"
    import.field13 = "grand_total,n,12,3,0"
    import.field14 = "data,d,13,8,0"
    import.field15 = "tracking,c,14,1,0"
    import.field16 = "status,c,15,7,0"
    import.field17 = "payment_type,c,16,15,0"
    import()


    a_tbl = table.open(import_destination_table)
    append.t_db = temporary_import_table
    append.m_key = ""
    append.t_key = ""
    append.m_filter = ""
    append.t_filter = ""
    append.type = "All"
    append.m_count = 17
    append.m_field1 = "ORDERID"
    append.m_exp1 = "@tbl_temp_orders_temp_table->ORDERID"
    append.m_field2 = "DATA"
    append.m_exp2 = "@tbl_temp_orders_temp_table->DATA"
    append.m_field3 = "NAME"
    append.m_exp3 = "@tbl_temp_orders_temp_table->NAME"
    append.m_field4 = "PHONE"
    append.m_exp4 = "@tbl_temp_orders_temp_table->PHONE"
    append.m_field5 = "ADRESS"
    append.m_exp5 = "@tbl_temp_orders_temp_table->ADRESS"
    append.m_field6 = "CITY"
    append.m_exp6 = "@tbl_temp_orders_temp_table->CITY"
    append.m_field7 = "POSTAL_CODE"
    append.m_exp7 = "@tbl_temp_orders_temp_table->POSTAL_CODE"
    append.m_field8 = "COUNTRY"
    append.m_exp8 = "@tbl_temp_orders_temp_table->COUNTRY"
    append.m_field9 = "E_MAIL"
    append.m_exp9 = "@tbl_temp_orders_temp_table->E_MAIL"
    append.m_field10 = "REFERENCE"
    append.m_exp10 = "@tbl_temp_orders_temp_table->REFERENCE"
    append.m_field11 = "NUM_ITEM"
    append.m_exp11 = "@tbl_temp_orders_temp_table->NUM_ITEM"
    append.m_field12 = "SUBTOTAL"
    append.m_exp12 = "@tbl_temp_orders_temp_table->SUBTOTAL"
    append.m_field13 = "CURRENCY"
    append.m_exp13 = "@tbl_temp_orders_temp_table->CURRENCY"
    append.m_field14 = "GRAND_TOTAL"
    append.m_exp14 = "@tbl_temp_orders_temp_table->GRAND_TOTAL"
    append.m_field15 = "STATUS"
    append.m_exp15 = "@tbl_temp_orders_temp_table->STATUS"
    append.m_field16 = "PAYMENT_TYPE"
    append.m_exp16 = "@tbl_temp_orders_temp_table->PAYMENT_TYPE"
    append.m_field17 = "TRACKING"
    append.m_exp17 = "@tbl_temp_orders_temp_table->TRACKING"
    append.t_count = 0
    a_tbl.append()


    GOTO CONTINUE1001201520165984
    ERROR1001201520165984:
    ON ERROR GOTO 0
    ui_msg_box("Error","Error running Import Operation"+crlf()+error_text_get())
    END
    CONTINUE1001201520165984:

    a_tbl.close()

    file_add_to_db(table.filename_get(import_destination_table))
    'If the Operation is run from within a Form or Browse, then refresh the window


    ' ================= I create the Tbl_temp_headRF


    'where I add fields ORDERID and delete duplicates
    'then do an append on tbl_Head_RF of OrderID and DATA

    copy_db_name = filename_decode("[PathAlias.ADB_Path]\tbl_temp_headRf.dbf")

    a_tbl = table.open("tbl_temp_orders")
    ON ERROR GOTO ERROR11012015210915170


    DIM a5_operation_order as C
    a5_operation_order = ""
    query.order = a5_operation_order
    DIM a5_operation_filter as C
    a5_operation_filter = ".T."
    query.filter = a5_operation_filter
    query.options = "I"
    query.description = "Temporary Query"
    i_indx = a_tbl.query_create()

    copy.set = ""
    copy.db = copy_db_name
    copy.dd = .T.
    copy.delete_o_dd = .T.
    copy.records = .T.
    copy.fields = 2
    copy.field1 = "Orderid"
    copy.field2 = "Data"

    a_tbl.copy()


    GOTO CONTINUE11012015210915170
    ERROR11012015210915170:
    ON ERROR GOTO 0
    ui_msg_box("Error","Error running Copy Operation"+crlf()+error_text_get())
    END
    CONTINUE11012015210915170:
    a_tbl.close()
    'Add the table just created by the Copy operation to the Database..
    file_add_to_db(copy_db_name)

    ' ===============quindi dalla tbl_Temp_HeadRf
    ' delete dupicate for ORDERID

    a_tbl = table.open("tbl_temp_headRf")
    ON ERROR GOTO ERROR11012015212512308


    a_tbl.delete_dups("str(ORDERID,7,0)",".T.")


    GOTO CONTINUE11012015212512308
    ERROR11012015212512308:
    ON ERROR GOTO 0
    ui_msg_box("Error","Error running Mark/Un-mark/Delete Operation"+crlf()+error_text_get())
    END
    CONTINUE11012015212512308:
    a_tbl.close()


    '==========Append the data from Tbl_Temp_HeadRf
    ' into a tbl_HeadRF that have an auto-increment field
    ' this is for to create a uniqe and sequential invoice_number
    DIM Append as P

    a_tbl = table.open("tbl_headrf")


    append.t_db = "tbl_temp_headrf"
    ON ERROR GOTO ERROR11012015222717647


    append.m_key = ""
    append.t_key = ""
    append.m_filter = ""
    append.t_filter = ""
    append.type = "All"
    append.m_count = 3
    append.m_field1 = "Orderid"
    append.m_exp1 = "@Tbl_Temp_Headrf->Orderid"
    append.m_field2 = "Data"
    append.m_exp2 = "@Tbl_Temp_Headrf->Data"
    append.m_field3 = "Data_Ric_Fisc"
    append.m_exp3 = "date()"

    append.t_count = 0

    a_tbl.append()


    GOTO CONTINUE11012015222717647
    ERROR11012015222717647:
    ON ERROR GOTO 0
    ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
    END
    CONTINUE11012015222717647:
    a_tbl.close()

    '=============== a questo punto copio i dati dalla Tbl_Temp_Orders
    ' sulla tabella Tbl__Articoli


    copy_db_name = filename_decode("[PathAlias.ADB_Path]\tbl__articoli.dbf")

    a_tbl = table.open("tbl_temp_orders")
    ON ERROR GOTO ERROR13012015092622296


    DIM a5_operation_order as C
    a5_operation_order = ""
    query.order = a5_operation_order
    DIM a5_operation_filter as C
    a5_operation_filter = ".T."
    query.filter = a5_operation_filter
    query.options = "I"
    query.description = "Temporary Query"
    i_indx = a_tbl.query_create()

    copy.set = ""
    copy.db = copy_db_name
    copy.dd = .T.
    copy.delete_o_dd = .T.
    copy.records = .T.
    copy.fields = 26
    copy.field1 = "Orderid"
    copy.field2 = "Data"
    copy.field3 = "Name"
    copy.field4 = "Phone"
    copy.field5 = "Adress"
    copy.field6 = "City"
    copy.field7 = "Postal_Code"
    copy.field8 = "Country"
    copy.field9 = "E_Mail"
    copy.field10 = "Reference"
    copy.field11 = "Num_Item"
    copy.field12 = "Subtotal"
    copy.field13 = "Currency"
    copy.field14 = "Grand_Total"
    copy.field15 = "Shipping"
    copy.field16 = "Status"
    copy.field17 = "Payment_Type"
    copy.field18 = "Tracking"
    copy.field19 = "Note"
    copy.field20 = "Orderinfo"
    copy.field21 = "Product_Id"
    copy.field22 = "Cliente_Id"
    copy.field23 = "Id_Pay_Method"
    copy.field24 = "Param"
    copy.field25 = "Rate"
    copy.field26 = "Suma_Pln"

    a_tbl.copy()


    GOTO CONTINUE13012015092622296
    ERROR13012015092622296:
    ON ERROR GOTO 0
    ui_msg_box("Error","Error running Copy Operation"+crlf()+error_text_get())
    END
    CONTINUE13012015092622296:
    a_tbl.close()
    'Add the table just created by the Copy operation to the Database..
    file_add_to_db(copy_db_name)





    with this code Ithe file compile everything. The only problem now is that the table for numbering of invoice after import some data, this numbering do no start from 1
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  6. #66
    Member Cristiano's Avatar
    Real Name
    Cristiano
    Join Date
    Jan 2007
    Location
    Venice (Italy)
    Posts
    384

    Default Re: Importing data an auto-increment field

    Nothing that a simple and trivial Excel file to compile the invoices in automatic.

    That's all
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  7. #67
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Importing data an auto-increment field

    Wow..really? This thread is gone this long?!
    This matter should be resolved with few lines of code.
    Post a sample of your Excel file and the desired alpha table.

  8. #68
    Member Cristiano's Avatar
    Real Name
    Cristiano
    Join Date
    Jan 2007
    Location
    Venice (Italy)
    Posts
    384

    Default Re: Importing data an auto-increment field

    Quote Originally Posted by G Gabriel View Post
    Wow..really? This thread is gone this long?!
    This matter should be resolved with few lines of code.
    Post a sample of your Excel file and the desired alpha table.
    Hello, and thanks for writing,

    in fact, from a simple Excel file that is downloaded every day. This Excel file should be
    automatically fill the tables to do invoices that must have a sequential counter.
    I will not give you the files because I do not want that you make me this job, because the job is about ending with all the help of this message board, and I want, again, to
    thank all friends
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  9. #69
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,200

    Default Re: Importing data an auto-increment field

    Cristiano,
    I have been tracking this thread. Many or most of us could solve this problem for you if we had what G and others have asked for. Why did you bother to post your code and unwilling to give the resources that folks have asked for? What is it that you expect the folks on this MB to do with the code you posted? You are seemingly not able to solve this problem on your own. That so many folks have tried to help you, and in what I read, you are not really helping this group to try and help find a solution. It makes me sad to see so many people trying to help, and you who seemingly are unable to achieve the solution, are deciding what help you should be receiving. It is this type of behavior that can bring negative considerations toward you, Cristiano. I believe it prudent that a MB Moderator close this thread.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  10. #70
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Importing data an auto-increment field

    And the sad part is, I do this very same thing on a daily basis, in fact a lot more complicated as I first download data from 2 websites to an Excel file (have very complicated dynamic charts in Excel hard to duplicate in alpha) then to alpha with one mouse click, yes, one click.
    I didn't ask for the actual Excel file nor the actual alpha file, just a sample of each.
    Of course, by now, I lost my appetite.
    Best of luck.

  11. #71
    Member Cristiano's Avatar
    Real Name
    Cristiano
    Join Date
    Jan 2007
    Location
    Venice (Italy)
    Posts
    384

    Default Re: Importing data an auto-increment field

    Hi,

    You know what Mike and Gabriel, this little problem was solved a few posts ago with the help of Ray, Tom, Dave M (thanks again to all ) and thanks also to you.

    I think that I do not post never again into this MB for the arrogance of someone, but I will continue to develop with Alpha Five
    Good luck to you. (Arrivederci)
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  12. #72
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,366

    Default Re: Importing data an auto-increment field

    Just followed this "Book at Bed Time".
    I'm not proud to accept help C, and nor should you.
    All of these guys have assisted me in the past, so don't wander off just because you have taken offence over one comment.
    We all have bad days and feel bruised, but suggest you hang on in there as if you are planning to continue with AS, you will definitely need help from the forum.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  13. #73
    Member Cristiano's Avatar
    Real Name
    Cristiano
    Join Date
    Jan 2007
    Location
    Venice (Italy)
    Posts
    384

    Default Re: Importing data an auto-increment field

    Quote Originally Posted by Ted Giles View Post
    Just followed this "Book at Bed Time".
    I'm not proud to accept help C, and nor should you.
    All of these guys have assisted me in the past, so don't wander off just because you have taken offence over one comment.
    We all have bad days and feel bruised, but suggest you hang on in there as if you are planning to continue with AS, you will definitely need help from the forum.
    Hello and thank you for having responded,

    I took a few hours of time to think before answering. I follow and I will follow always the forum. Do not think I've offended anyone by failing to respond properly to all, but if so I apologize.
    Now my script to AUTOMATE the work is finished, (There are no masks, and the only thing that the user needs to do is press a button to print invoices duly compiled, he need only to prepare and ship the orders. Certainly it has been thanks to the help of all those who have written here

    Thanks again guys
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

Similar Threads

  1. auto increment field
    By ChilliCrab in forum Windows Desktop Applications
    Replies: 0
    Last Post: 01-09-2014, 12:10 AM
  2. Auto Increment field
    By deputy956 in forum Alpha Five Version 10 - Desktop Applications
    Replies: 2
    Last Post: 08-08-2013, 01:51 PM
  3. Auto increment ID field - load prior data
    By jdtwright in forum Alpha Five Version 7
    Replies: 4
    Last Post: 02-05-2006, 01:35 AM
  4. Auto Auto-Increment Field
    By Ken McEntee in forum Alpha Five Version 5
    Replies: 8
    Last Post: 05-12-2004, 01:12 PM
  5. replacing data in an auto increment field
    By jay kirschner in forum Alpha Four Version 6 and Prior
    Replies: 1
    Last Post: 11-02-2001, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •