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

    #16
    Re: Importing data an auto-increment field

    Cristiano
    See the attached.
    First screen is your import temp file, second is an Invoice header file with autoincrement invoice number and index called ORDER on the order field.
    Third is a few lines of code that creates the order header record. Run the code.
    You will make a set, with invoice as the first and your collected orders table one to many common field ORDER.
    Make a default form and you will see as you go from one invoice number to the next all the orders belonging to the invoice will appear.
    You can invoice based on that set, and flag those invoices that have been processed. Add some appropriate fields to the invoice (header) table

    Remember to close the tables after you have done what you need with them.
    Attached Files
    Last edited by Ray in Capetown; 01-11-2015, 09:43 AM. Reason: added screenshot

    Comment


      #17
      Re: Importing data an auto-increment field

      You don't need a field in the orders table for the invoice number unless you have other reasons for that.
      A set and simple default form with all fields will illustrate the basics
      Attached Files

      Comment


        #18
        Re: Importing data an auto-increment field

        C,

        Please answer these questions:

        a) do your invoices need both an invoice number and an order number? ( Ray's example script uses the order number and illustrates how to create necessary header table records, but does not assign invoice numbers. Presumably his invoice numbers in the newly created records are supplied by an auto-increment rule in the header table. This approach may be exactly what you need since the numbering of newly created header records will simply continue the sequence already in place in that table. )

        b) after you import order items from the excel sheet how will the system know which invoice numbers to assign? i.e. if you've imported items from 5 orders how will Alpha know what invoice number to give the items in the first imported order?
        Last edited by Tom Cone Jr; 01-11-2015, 11:59 AM. Reason: Clarify original text

        Comment


          #19
          Re: Importing data an auto-increment field

          Look again Tom
          there is definitely a unique incrementing invoice number, thats a fundamental point of the original post.
          Strangely, called "Invoice"
          What I said in my last post is that this invoice number does not HAVE to necessarily be inserted in the order table unless there is additional need.

          It shows in the screenshot post #17
          Last edited by Ray in Capetown; 01-11-2015, 11:18 AM.

          Comment


            #20
            Re: Importing data an auto-increment field

            Ray, if I've posted something that's wrong please let me know, but be specific. Telling me to "look again" isn't adequate. Subtleties are lost on me, or so I've been told. Thanks. < grin >

            Comment


              #21
              Re: Importing data an auto-increment field

              Sure Tom
              Sorry I though I was specific. In my example the number attached to the field INVOICE (look again at screenshot in Post#17 is what I said) is what Christiano asked for as an autoincrement in his original query and this one which he wasnt able to get. Your one point suggests a header, which is necessary for this so I made that create the incremental number asked for.
              Originally posted by Cristiano View Post
              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
              He numbers them here as 1, 2, 3, 4

              Comment


                #22
                Re: Importing data an auto-increment field

                Ray,

                You've posted only one script. i.e. in post 16. This script creates new header records using the order number field, not an "invoice number" field. There's nothing wrong with that. It is this aspect of your script that I was referencing. Sorry I wasn't clearer. I've edited the previous post for clarification.
                Last edited by Tom Cone Jr; 01-11-2015, 12:00 PM.

                Comment


                  #23
                  Re: Importing data an auto-increment field

                  Yes I see - not explicit enough it appears, trying at the same time to be succinct, repeated below, I mentioned that the invoice number field is autoincrement - meaning as well by implication that its therefore not required in the code. I also I suppose am possibly over-estimating Cristianos understanding of my English. Please ask C if this is the case.
                  Originally posted by Ray in Capetown View Post
                  Cristiano
                  See the attached.
                  First screen is your import temp file, second is an Invoice header file with autoincrement invoice number, and index called ORDER on the order field.
                  Make a default form and you will see as you go from one invoice number to the next all the orders belonging to the invoice will appear.
                  ....

                  Comment


                    #24
                    Re: Importing data an auto-increment field

                    Is there any benefit, to anyone if I post the example ADB I created for this?

                    Comment


                      #25
                      Re: Importing data an auto-increment field

                      Hi guys,

                      I think Gary is right. I need to split the Excel file in two temporary tables where the first table is the table for the invoice header with the serial number of the invoice and the number of unique order (obviously I have to delete the double order numbers) and I have to create another table related with the Invoice-header table that contains the rest of the fields of the Excel file including the numbers of orders.

                      Do you think it could work?
                      Cristiano
                      [email protected]

                      Skype: cris-do

                      Comment


                        #26
                        Re: Importing data an auto-increment field

                        C,

                        Yes it could work. This depends on what "it" might be, and on that I remain confused. I've been trying to understand you, but you don't answer my questions, so I guess I'll get out of the way. Good luck with your project. Bye.

                        Comment


                          #27
                          Re: Importing data an auto-increment field

                          Originally posted by Tom Cone Jr View Post
                          C,

                          Yes it could work. This depends on what "it" might be, and on that I remain confused. I've been trying to understand you, but you don't answer my questions, so I guess I'll get out of the way. Good luck with your project. Bye.
                          I'm Sorry Tom but I think it is clear what are my needs.

                          The purpose of all this is the writing and printing of invoices, and all this mess is because I have no direct access to a database. (MYSSQL)

                          So my client (he one that has to make the bills) receives from its central office an Excel file with the orders of customers who buy from all over Europe. My client asked, then, to me to create a program that for him, starting from the Excel file can generate invoices.

                          I hope that now you have clear what I should do
                          Cristiano
                          [email protected]

                          Skype: cris-do

                          Comment


                            #28
                            Re: Importing data an auto-increment field

                            Ray, I see where you are going there. Each time you import and make a temp table, do you use the same temp table or a new one? do you overwrite the old temp? Does Christiano need to keep all of it in a table?
                            If you create a new table each time, would you have to add that table to the set?


                            Christiano,

                            First field is the real ai field

                            AI Field
                            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

                            bring the excel(or other file) into a temp table where you will have all the 11-12 fields, then append them to the table that already has the ai field. You can still index any fields needed and use the fields in any form, report, etc. The ai field is strictly to keep them together and in base order. Users never have to see the ai field at all unless you want them to.

                            You can not have identicals in an ai field! The entries have to be unique.
                            Last edited by DaveM; 01-11-2015, 03:03 PM.
                            Dave Mason
                            [email protected]
                            Skype is dave.mason46

                            Comment


                              #29
                              Re: Importing data an auto-increment field

                              C,

                              Yes, you've done a good job explaining the big picture. What you need is clear. The context in which this would run, and the specifics of the desired output, not clear. Questions posed previously, and ignored so far, would provide necessary details so that a solution might be offered. But you disagree. So be it.

                              Comment


                                #30
                                Re: Importing data an auto-increment field

                                Originally posted by Tom Cone Jr View Post
                                C,

                                Yes, you've done a good job explaining the big picture. What you need is clear. The context in which this would run, and the specifics of the desired output, not clear. Questions posed previously, and ignored so far, would provide necessary details so that a solution might be offered. But you disagree. So be it.

                                No Dear Tom, I don't think I've ignored any suggestion, indeed, every response on your part has been very useful, probably not I gave answer to all because I am dedicated to writing the code (given the short delivery times)
                                The situation is this:
                                There is a Headquarter, and several satellite offices around Europe. The problem arises from the fact that the headquarter doesn't want to give absolutely to their offices the db (don't ask me why, I have already quarrelled enough for that). Their only release an Excel file and I have to pull off the classic rabbit from the Hat
                                Cristiano
                                [email protected]

                                Skype: cris-do

                                Comment

                                Working...
                                X