Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Importing data an auto-increment field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    #61
    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
    [email protected]

    Skype: cris-do

    Comment


      #62
      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

      Comment


        #63
        Re: Importing data an auto-increment field

        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
        [email protected]

        Skype: cris-do

        Comment


          #64
          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, 12:43 PM. Reason: best to add imf.fetch_first() as left out first

          Comment


            #65
            Re: Importing data an auto-increment field

            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
            [email protected]

            Skype: cris-do

            Comment


              #66
              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
              [email protected]

              Skype: cris-do

              Comment


                #67
                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.

                Comment


                  #68
                  Re: Importing data an auto-increment field

                  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
                  [email protected]

                  Skype: cris-do

                  Comment


                    #69
                    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"

                    Comment


                      #70
                      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.

                      Comment


                        #71
                        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
                        [email protected]

                        Skype: cris-do

                        Comment


                          #72
                          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.
                          See our Hybrid Option here;
                          https://hybridapps.example-software.com/


                          Apologies to anyone I haven't managed to upset yet.
                          You are held in a queue and I will get to you soon.

                          Comment


                            #73
                            Re: Importing data an auto-increment field

                            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
                            [email protected]

                            Skype: cris-do

                            Comment

                            Working...
                            X