Duplicating records issue

[Samantha Mattie]

The company I work for sorts all the orders by a four digit order number. Several times a day we need to go in and alter these orders or in my case import more information from an excel file into the database. The problem I am having it, when I put in Order number equal order number and then import a specific field, instead of adding it to the record we have now and updating it, it creates a new record. This is becoming problematic when we have over 3000 records before the duplication. How can I stop this from happening and placing the information on the clients record without having several records for each field I update?

[LaRetta]

Samantha, there are three things to consider:

1) When you import, you have three import options. You will want to update MATCHING records. Match the Invoice number and click the dash between them until it turns into a heavy equal (indicating Match Records Based on this field).

2) When you import, it's critical to be sure all records are shown first by including a Show All Records. Because if you don't, and the target record is not in the found set, it won't update it. And be sure the 'Add Remaining Data' is NOT selected.

3) Are the invoice numbers exact? If they are numbers coming in, as 344 and in your FM table they are text as 0344, they will not be considered the same invoice number. Excel is notorious for attempting to think for the User and reformat data as it thinks you want it. Be sure the Invoice Numbers coming in are exact same format.

LaRetta :wink2:

[Samantha Mattie]

Thank you! Though I have another question, I have a feild like options for the client, now if the client orders more than one, I need to have all of the orders options on there, yet, it's always overridden, and I can't have more than one field with the same name. How can I get around this also?

[LaRetta]

I would need more specific information. But imports only work field to field. Once it's within FM, you can use techniques such as Auto-Enter (Replace) or script to move the data about. In Excel, are these options in different columns (fields) or all within the same cell?

If they are in the same cell, I suggest you import into a holding field in FM then decide what to do with it. BUT ... if your clients have more than one option, but your FM solution only has one field for the option, you're in trouble anyway, right? Can you describe this option field for me?

You might want holding text fields as Option1, Option2, and Option3. But your REAL fm field is called OPTIONS. Import always into the Option# fields. Then run looping script (while you still have your imported found set) to combine them and write further to the Option field, as:

Go To Record/Request/Page [ First ] ... probably not necessary as your import will leave you on first record, but I always like being safe here ...

Loop

Set Field [ Options ; TrimAll ( Option1 & " " & Option2 & " " & Option3 ; 1 ; 1 ) ]

Go To Record/Request/Page [ Exit after Last; Next ]

End Loop

(untested ... I need to leave)

If you need further help, others can assist. There are many wonderful people here. Otherwise, I'll check back in when I get home late tonight.

WHOA! If your client ORDERS more than one option? It sounds to me like these are items - like lineitems? More than one ITEM ordered? I need to know your structure. You should have Invoice and LineItems table. Simply, I can't properly advise until I know a bit more...

LaRetta

Edited December 1, 2005 by Guest

[Samantha Mattie]

We have items, b ut these items have several options, like if they want a custom message, Custom Ribbon, 1 or 2 photos and this can be for one item. If they want all of these options, when I download it from the yahoo store and upload it into the database we have for the inner office, each option is it's own record instead of keeping it all together.

I do hope this makes sense in how I'm describing my issue.

Samantha

[LaRetta]

Hi Samantha,

Can you provide a file showing your existing structure and a sample of the Excel import? I have no idea if you have a one-to-many established to handle multiple Items for one order; or multiple options for one item. Do you currently have a Products table or Customer table? Do you have an Order table related to a LineItems table?

Without knowing your structure, we're just providing fluff and could actually increase your problems instead of helping you. I'll tell you one thing ... Excel is flat-file so my sense is that you might benefit from an intermediate import table; in which the data is converted before moving it into your final solution.

"placing the information on the clients record without having several records for each field I update? "

It may work best to import these 'options' from Excel into a related OPTIONS table which is joined on the Invoice Number AND ItemNumber to your Order table. In this way, you will have a 1:n (one-to-many) Item to Options structure. We will help you figure this out. But we need to know what we're 'figuring.'

LaRetta