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

  • Importing data an auto-increment field

    Hi Guys,

    I have this problem:
    I have an Excel file (attached) where there are customer-generated orders. I can't seem to create an Alpha Five table with an AutoNumber field but that, for the same order number, the counter field is not increment its value.

    Do you have any suggestion on how to make this ?


    Thank you
    Attached Files
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  • #2
    Re: Importing data an auto-increment field

    Please clarify.

    Are you trying to import the Excel file and then begin autonumbering with the next field value when additional records are entered?

    Are you trying to import the Excel file and preserve the existing number field values, but Alpha is changing them... ?

    Are you trying to import the Excel file and autonumber each imported record with a new, consecutive, field value?

    Comment


    • #3
      Re: Importing data an auto-increment field

      Hi and thank you for responding.

      If you look at the file in Excel the customer 1 has ordered three things and have the same order number also the customer 2 has ordered two two things (and has another order number), and so on
      Now, if I import this Excel file into a table of Alpha Five that has an auto-increment counter field, the result is that the customer 1 will always have the same order numbers but with a field counter with an auto-increment value

      Instead I need into this table of Alpha Five, for the same value of order there is a single value in the counter, then the result table of Alpha Five should be this:

      auto-increment field
      1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 1 2 70 EUR
      1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 2 1 35 EUR
      1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 3 1 35 EUR
      1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 4 1 27 EUR
      2 1230645 Customer name 2 36804863213 Via per di qua City 1 40128 IT emailclients Item 2 1 43 EUR
      3 1230646 Customer name 2 36804863213 Via per di qua City 1 40128 IT emailclients Item 4 1 88 EUR
      4 1230660 Customer name 3 3404863218 Via Roncaglio 422 City 2 40185 IT emailclients Item 5 1 25 EUR

      I must absolutely use a counter field because this counter field is used as a counter to the creation of invoices

      Hoping now is all more clear
      Cristiano
      cristianodona@gmail.com

      Skype: cris-do

      Comment


      • #4
        Re: Importing data an auto-increment field

        Cristiano,

        An autoincrement field is just that. It automatically increments for each record. You can't use that to be a counter field for related records.

        There are ways to automatically create the counter value via xbasic as the records are entered.

        The easiest way to show you how to do that would be to have a sample of your table.
        There can be only one.

        Comment


        • #5
          Re: Importing data an auto-increment field

          Thank you Steve,


          If you import the Excel file that i attached into a new table, you have the table that you required.



          Can you show me a example ?
          Cristiano
          cristianodona@gmail.com

          Skype: cris-do

          Comment


          • #6
            Re: Importing data an auto-increment field

            I don't have Excel available today.

            Create a new table with all the fields you want to have from the excel file. Code the onsaverecord event in the field rules for a table you create.

            In the sample code below the table name is inv. The customer identifier field is custid. The invoice number field is invoice. The invoice line counter field is lineno. You need to change the code to match your table.

            tbl = table.current()
            tbl.change_begin()
            tbl.lineno =tablecount("inv","custid+invoice = "+quote(tbl.custid+tbl.invoice))
            tbl.change_end()

            Import your excel file to a temporary table and then append to the table you have created.

            As each record is saved the code should fill in the lineno (counter) field.
            There can be only one.

            Comment


            • #7
              Re: Importing data an auto-increment field

              C,

              I imported your spreadsheet to a new table. Naturally, none of the fields in the new table are set to autoincrement. Since the result table matches what you have described in post # 3 I remain confused about your goal. Please post a representative sampling of rows showing what you actually want to achieve.

              Comment


              • #8
                Re: Importing data an auto-increment field

                Thank you Stan


                I update this code and then I tell you

                Cristiano
                Cristiano
                cristianodona@gmail.com

                Skype: cris-do

                Comment


                • #9
                  Re: Importing data an auto-increment field

                  Hi Tom,

                  Thank yu for your reply, I try to implement the Stan suggestion.
                  Cristiano
                  cristianodona@gmail.com

                  Skype: cris-do

                  Comment


                  • #10
                    Re: Importing data an auto-increment field

                    I think, unless I am way off in my opinion and reading.

                    The final table would have something like this:

                    101 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 1 2 70 EUR
                    102 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 2 1 35 EUR
                    102 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 3 1 35 EUR
                    103 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 4 1 27 EUR
                    104 2 1230645 Customer name 2 36804863213 Via per di qua City 1 40128 IT emailclients Item 2 1 43 EUR
                    104 3 1230646 Customer name 2 36804863213 Via per di qua City 1 40128 IT emailclients Item 4 1 88 EUR
                    105 4 1230660 Customer name 3 3404863218 Via Roncaglio 422 City 2 40185 IT emailclients Item 5 1 25 EUR

                    Since an ai field can not have duplicates or it would not be autoincrement. Thus when an ai field is set an index is created that is unique.

                    I do a lot of this as many other do. I do like Tom and create a temp table to import into and then append the results to the table where needed. I think most do it this way.
                    Dave Mason
                    dave@aldadesktop.com
                    Skype is dave.mason46

                    Comment


                    • #11
                      Re: Importing data an auto-increment field

                      Hi guys,


                      @Stan

                      I tested your code that you suggested, but the field lineno was not filled in with any value
                      Cristiano
                      cristianodona@gmail.com

                      Skype: cris-do

                      Comment


                      • #12
                        Re: Importing data an auto-increment field

                        Originally posted by DaveM View Post
                        I think, unless I am way off in my opinion and reading.

                        The final table would have something like this:

                        101 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 1 2 70 EUR
                        102 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 2 1 35 EUR
                        102 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 3 1 35 EUR
                        103 1 1230643 Customer name 1 3404863213 Via Roncaglio 41 City 1 40128 IT emailclients Item 4 1 27 EUR
                        104 2 1230645 Customer name 2 36804863213 Via per di qua City 1 40128 IT emailclients Item 2 1 43 EUR
                        104 3 1230646 Customer name 2 36804863213 Via per di qua City 1 40128 IT emailclients Item 4 1 88 EUR
                        105 4 1230660 Customer name 3 3404863218 Via Roncaglio 422 City 2 40185 IT emailclients Item 5 1 25 EUR

                        Since an ai field can not have duplicates or it would not be autoincrement. Thus when an ai field is set an index is created that is unique.

                        I do a lot of this as many other do. I do like Tom and create a temp table to import into and then append the results to the table where needed. I think most do it this way.


                        Hi Dave thank you for reply, but sorry what are the numbers 101,102,103,104 and 105 ?


                        Thank you
                        Cristiano
                        cristianodona@gmail.com

                        Skype: cris-do

                        Comment


                        • #13
                          Re: Importing data an auto-increment field

                          Originally posted by Tom Cone Jr View Post
                          C,

                          I imported your spreadsheet to a new table. Naturally, none of the fields in the new table are set to autoincrement. Since the result table matches what you have described in post # 3 I remain confused about your goal. Please post a representative sampling of rows showing what you actually want to achieve.

                          Hi Tom and thank you .

                          The question is a little bit problematic because this Excel file is the result of an extraction from an MYSQL Query and we cannot have access to this query . This query is the result of a series of orders placed by customers. An office (in Italy) need to make and send some invoices starting to this Excel file. Naturally into this Excel file there's not an invoice number and the invoice number be an unique number and sequential number .So. If you see my Excel file, the customer name 1 has ordered four products, the customer name 2 has ordered two products and the customer name 3 has ordered only one product:

                          I need to create a table that use a unique and sequential number ( invoice numbers) and in the sample is :

                          Invoice number 1 for order number 1230643
                          invoice number 2 for order number 1230645
                          invoice number 3 for order number 1230660

                          I'm sorry, but I see now that into the Excel there is an error, this order number is wrong: 1230646 and is 1230645 and is part of order for customer name 2


                          I Hope now is more clear
                          Cristiano
                          cristianodona@gmail.com

                          Skype: cris-do

                          Comment


                          • #14
                            Re: Importing data an auto-increment field

                            C,

                            You have a couple of problems tangled together.

                            1) An import to a new table won't create a field (column) for the invoice number you need. So, Stan's idea will help overcome this. Import to a temp table and then append from the temp table to a table that includes a field (column) for the invoice number you need to populate. At that point the invoice numbers are NOT YET populated. If you were hoping the autoincrement field rule would do that forget it. It can't.

                            2) if the receiving table is sorted by the order number you could run a script that would assign invoice numbers to the items in an order. All items in an order would receive the same invoice number, but each order would have a unique and consecutive invoice number. For such a thing to work the script would have to know what invoice number to begin with each time.

                            3) You will probably face the additional problem of creating invoice header records in a related table. Since it's likely the invoices themselves will be based on a set containing a header table linked to an items table.

                            Comment


                            • #15
                              Re: Importing data an auto-increment field

                              Hi Tom,

                              thank you for reply, but maybe I do not understand.

                              First of all I know is impossible to use an auto-increment field for my problem, but I Don't understand what I have to do . I need to create a table for the number of invoice that fill in the table of orders with his invoice number ?
                              Could you make me a real sample based on Excel file ?
                              Cristiano
                              cristianodona@gmail.com

                              Skype: cris-do

                              Comment

                              Working...
                              X