Alpha Video Training
Page 2 of 3 FirstFirst 123 LastLast
Results 31 to 60 of 73

Thread: Importing data an auto-increment field

  1. #31
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Importing data an auto-increment field

    Christiano,

    Are they giving you an account number for these excel files and is each excel file associated with only one account?
    The header table like Ray gave could be helpful if there is an account/control field for each file.
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  2. #32
    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 DaveM View Post
    Christiano,

    Are they giving you an account number for these excel files and is each excel file associated with only one account?
    The header table like Ray gave could be helpful if there is an account/control field for each file.


    Hi Dave,
    as I said the headquarters of this company does not want to give access to their database absolutely (I think it's because they want that the satellite offices they buy their software management) of 32 offices throughout Europe only 2 offices they use my software, the other 30 are considering buying the parent company's management software, also why only a fool like me could create an application from a simple Excel file.

    Another little problem that I'm encountering is that the field "invoice_number" (number field) that I use as a counter for creating invoices start from "0" instead of "1". In Italy the numbering for tax bills must start from 1 and not from 0.

    The tips I received in this post, were very useful and instructive and so thank you everybody.

    These are the tables that I have created from the Excel file that I received:

    Tbl_Temp_Orders (Template where I imported all fields from Excel file)

    Tbl_Temp_header (Template where I copy only two fields: ORDERID and date from the table Tbl_Temp_Orders)

    from this last table, then delete duplicate records (a_tbl. delete_dups ("str (ORDERID .7 .0)", ".T.") )

    I have created another table for the numbering of invoices and the order numbers: Tbl_Header_Invoice. This Table I use for doing the numbering of invoices and the data it receives from the table Tbl_Temp_Header only after that I delete the duplicate records. (see above)

    Always in Tbl_Invoice_Header table I have created an auto-increment numbering field, this is a field is used for number of invoices. Here, though, I have a problem because the auto-numbering starts from 0 and not 1 and this for the Italian tax legislation is no good.


    Then I copy the rest of data from the Tbl_Temp_Orders tables to the final table: Tbl_Invoice and also the field ORDERID

    Finally I created a SET with Tbl_invoice_Header and Tbl_Invoice that have a common field: OrderID


    Now I finish to prepare it and then try, hope it works
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  3. #33
    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,


    And I would like to apologize to everyone for my terrible English
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  4. #34
    "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

    Dave the way the code is written is universal, he can add to the able or append to a main table after the code has run (the way Cristiano described he does already, is preferable) either would work
    But seems a waste of time if Cristiano is not interested in the ready to run simple solution provided. As you know doesn't have to be more complicated
    We do this sort of thing all the time.

  5. #35
    "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

    Cristiano this probably will work, difficult to maintain table size with constant deleting records for every single order
    Code:
    Tbl_Temp_Orders (Template where I imported all fields from Excel file) this is all you need
    
    Tbl_Temp_header (Template where I copy only two fields: ORDERID and date from the table Tbl_Temp_Orders) unnecessary
    
    from this last table, then delete duplicate records (a_tbl. delete_dups ("str (ORDERID .7 .0)", ".T.") ) ' IMO bad practice. I suggested just adding the new order number once.

  6. #36
    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 DaveM View Post
    Christiano,

    Are they giving you an account number for these excel files and is each excel file associated with only one account?
    The header table like Ray gave could be helpful if there is an account/control field for each file.

    Hi Dave,
    The only file that we have in our hand is the Excel file.
    nothing but this wretched file with customer orders. I'm building a solution for printing invoices starting from a simple and banal Excel file. This is really frustrating.
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  7. #37
    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
    Cristiano this probably will work, difficult to maintain table size with constant deleting records for every single order
    Code:
    Tbl_Temp_Orders (Template where I imported all fields from Excel file) this is all you need
    
    Tbl_Temp_header (Template where I copy only two fields: ORDERID and date from the table Tbl_Temp_Orders) unnecessary
    
    from this last table, then delete duplicate records (a_tbl. delete_dups ("str (ORDERID .7 .0)", ".T.") ) ' IMO bad practice. I suggested just adding the new order number once.
    Ok, Ray,
    but I need a numeric field that is increased to do the numbering of invoices , how can do it if I don't create a Tbl_header_invoice table?
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  8. #38
    "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

    To repeat one of Tom's questions
    The next incrementing number created with each order number, is THAT going to be the invoice number? Or it is a sort of tracking number and an invoice number will be created later or when invoiced?
    I created a database to work out the working example explained earlier.
    Do you want a copy of that database to play with, may be easier than explaining? Or do you want a walk-through with examples

  9. #39
    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
    To repeat one of Tom's questions
    The next incrementing number created with each order number, is THAT going to be the invoice number? Or it is a sort of tracking number and an invoice number will be created later or when invoiced?
    I created a database to work out the working example explained earlier.
    Do you want a copy of that database to play with, may be easier than explaining? Or do you want a walk-through with examples
    Dear Ray,

    i think was all clear The file Excel (as you see the Excel file) is for to PRINT the invoices, becaause there there are all the orders made by the customers in italy. I had to finish this part of job but I appreciate if you cangive me a real sample as i can check if my job is correct
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  10. #40
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Importing data an auto-increment field

    C,

    Please review post #18 in this thread. These questions remain unanswered. If you are having trouble understanding the questions let us know. Perhaps we can clarify things for you. -- tom

  11. #41
    "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

    Cristiano
    your excel file in .RAR file does not download here
    I made up something to work with.
    If you send a .dbf that you create first (from the Excel) I will send you a basic working model ADB that you can add more excel files to and see how they create the header and collected orders table that you use in a set for invoicing. Can you do?

  12. #42
    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
    Cristiano
    your excel file in .RAR file does not download here
    I made up something to work with.
    If you send a .dbf that you create first (from the Excel) I will send you a basic working model ADB that you can add more excel files to and see how they create the header and collected orders table that you use in a set for invoicing. Can you do?
    Ok Ray, now I'm not at work , tonight i send to you the table from excel
    Thank you
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  13. #43
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Importing data an auto-increment field

    On creation of a AI field in field rules, you can cause it to start with 1 by "Data Entry" then "default" choose simple and put a 1 in there. If character, precede with 0's for padding.
    Last edited by DaveM; 01-12-2015 at 12:52 PM.
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  14. #44
    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 Tom Cone Jr View Post
    C,have told

    Please review post #18 in this thread. These questions remain unanswered. If you are having trouble understanding the questions let us know. Perhaps we can clarify things for you. -- tom
    .

    I'm sorry Tom but Which are the questio remain unanswered ? I told that the excel is the only file tha I have and with that file i need to print invoices, and I agree with you (solution 3) regarding the creation on set based containing two: table, the fisrt with the invoice number and the Orderid and the second table with the same OrderId and with the detailed orders linked by the order id
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  15. #45
    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 DaveM View Post
    On creation of a AI field in field rules, you can cause it to start with 1 by "Data Entry" then "default" choose simple and put a 1 in there. If character, precede with 0's for padding.
    Hi Dave,

    I do it like you suggested, but when I tried to copy or append data from an Tbl_Temp_header table to the Tbl_header_Invoice the count of invoices start from 0. I'm sure I have wrong something but I don't know what
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  16. #46
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Importing data an auto-increment field

    C, I guess the language differences may present insurmountable barriers. If you look at post #18 in this message board thread you will see two questions (a) and (b). Neither have been answered. If you can't find post #18, or if you don't understand the questions, let us know.

  17. #47
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Importing data an auto-increment field

    Not to butt in, but you could create a dummy record with the value of 0 or "0" and then your append should increment beginning with 1. You can delete the dummy record later.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  18. #48
    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 Tom Cone Jr View Post
    C, I guess the language differences may present insurmountable barriers. If you look at post #18 in this message board thread you will see two questions (a) and (b). Neither have been answered. If you can't find post #18, or if you don't understand the questions, let us know.

    Hi dear Tom,

    definitely my English must be bad if until now I have not been able to tell you what I have to do, and I think that I've already explained what are the intentions for this my app. In fact, as I said in my post # 25 of the message board, I told that I would have implemented the solution proposed by Ray. So, by replying to what is required in your post # 18 in this message board, the answer is that I have to implement the solution a), but I have to use a field auto-incremented to assign the invoice number that I want to print.

    For your question b) I suppose that if I link the two tables with a common field (ORDERID) might work. The first table automatically generates the invoice number and the related field (ORDERID) is the field that link the second table with the details of the orders
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  19. #49
    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 Cristiano View Post
    Ok Ray, now I'm not at work , tonight i send to you the table from excel
    Thank you

    Hi Ray,

    I'm sorry for .rar file I use this for to compress files. Now in attach you can find a zip file

    Sorry again
    Attached Files Attached Files
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  20. #50
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Importing data an auto-increment field

    If you already have a ai in place with records in the table, you would have to remove the ai designation in field rules(also the index for the field that is unique, then use an operation to increment all the table records by 1. That would make your 0 be 1 and your 1 become 2, etc.
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  21. #51
    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 DaveM View Post
    If you already have a ai in place with records in the table, you would have to remove the ai designation in field rules(also the index for the field that is unique, then use an operation to increment all the table records by 1. That would make your 0 be 1 and your 1 become 2, etc.

    Hi Dave,

    Sorry but I do not understand ... My mistake I think was when I tried to update the table tbl_Header_invoice from the Temporary table.

    How can I then update a table trying to keep or make the file counter to start in the correct way?
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  22. #52
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Importing data an auto-increment field

    I will see if I have some free time to make you a video of the process?? I will zip it an place it here or give a download address for it.
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  23. #53
    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 DaveM View Post
    I will see if I have some free time to make you a video of the process?? I will zip it an place it here or give a download address for it.

    Thanks Dave,

    you can send a mail, in signature there is my email account. Perhaps is the best thing
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  24. #54
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Importing data an auto-increment field

    Cristiano,
    I know you didn't reply to my post, but if you are trying to append records to an empty table, the default field rule will not be invoked because it is for direct user entry. Therefore, setting the first record in the table with a default value of 0 first, can then be incremented by the append operation because it is now an indexed value.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  25. #55
    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 MoGrace View Post
    Cristiano,
    I know you didn't reply to my post, but if you are trying to append records to an empty table, the default field rule will not be invoked because it is for direct user entry. Therefore, setting the first record in the table with a default value of 0 first, can then be incremented by the append operation because it is now an indexed value.
    Hi,

    and sorry if I have not yet responded, in effect this is exactly was what I was thinking, to make an empty record ,but how do I create an empty record on a table that is auto-increment?
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  26. #56
    "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

    C,
    On the tab "Field types" you have checked the button auto-increment, go back there.
    Select the next tab "Data Entry" set default mode to "simple default expression" and the number to whatever first number you wish.
    If numbering has started then this number you choose must be at least higher than the last used.

  27. #57
    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
    C,
    On the tab "Field types" you have checked the button auto-increment, go back there.
    Select the next tab "Data Entry" set default mode to "simple default expression" and the number to whatever first number you wish.
    If numbering has started then this number you choose must be at least higher than the last used.
    Hi Ray ,

    I'm sorry but in the table hat I sent to you ( Tbl_Temp_Orders) There is no set field that way.
    Cristiano
    cristianodona@gmail.com

    Skype: cris-do

  28. #58
    "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

    I'm sorry but in the table hat I sent to you ( Tbl_Temp_Orders) There is no set field that way.
    1. Of course there isn't, tbl_tmp_orders doesn't have an autoincrement does it -you are asking about your increment starting number, look wherever that is
    2. I dont have tbl_tmp_orders.dbf did you send it? I see post 49 Ok missed that I was watching my email notifications must have skipped that

  29. #59
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Importing data an auto-increment field

    Not an empty record but a dummy record, that is one that only contains default values.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  30. #60
    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 MoGrace View Post
    Not an empty record but a dummy record, that is one that only contains default values.
    What does a record dummy? I've never created a how?
    I have to do in the table with the field that should be auto-incremented ?
    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
  •