Importing data an auto-increment field

[Cristiano]

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

[Tom Cone Jr]

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?

[Cristiano]

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

[Stan Mathews]

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.

[Cristiano]

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 ?

[Stan Mathews]

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.

[Tom Cone Jr]

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.

[Cristiano]

Re: Importing data an auto-increment field

Thank you Stan


I update this code and then I tell you

Cristiano

[Cristiano]

Re: Importing data an auto-increment field

Hi Tom,

Thank yu for your reply, I try to implement the Stan suggestion.

[DaveM]

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.

[Cristiano]

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]

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.

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


Thank you

[Cristiano]

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.

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

[Tom Cone Jr]

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.

[Cristiano]

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 ?