Add new fields to existing table and populate just those fields with an import or append?

[Hansolo]

I have an existing alpha table (Table1) with 8000 records with fields A thru M some of which are calculated fields. Data in an excel spreadsheet (Table2) collected by another system has some fields not included in Table1, say fields N thru Z. Table1's structure has been modified in design mode to include those new fields N thru Z. Table1 and Table2 share a unique identifier but I don't want to work with the tables in a one-to-one relationship, I would rather import the data from Table2 into the records of Table1 using the unique identifier for correct placement.

I have been trying to use operations to accomplish this but to little success. It seems that my only option is the import the excel Table2 into a new alpha table and then Join Table1 and Table2 into a new Table3. Of course all of the components, security and reports are based on Table1 so I guess I will have to rename the original Table1 to something like Table1_old and rename Table3 to Table1 and hope all the components and reports work???

So in explaining that pain staking process I am hoping to invoke a sympathy resolution that is simpler. Like and operation that allows me to identify the unique identifier, map the fields I need imported into the new impty fields created in Table one and import/append/update (whatever) only those fields to the existing records in Table1.

Append operation will import the new fields from Table2 to the proper records in Table1 but in the process it says it will erase the existing data in Table1 for any fields not imported. Why??

Does anyone have a simple solution or can point out an operation or option I am not finding?

Thank you.

[Al Buchholz]

Re: Add new fields to existing table and populate just those fields with an import or append?

Please supply where you found the idea that an append will blank out fields. That doesn't sound right to me.

Sounds like an append or update will do what you want.

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Here is a screen shot of the last screen of the builder before committing to the operation for an import of selected fields from one table (excel or Alpha dbf) to the master table (alpha dbf).
AlphaDAO import.jpg

Note:
If I chose for alpha not to check for duplicate records, it will import all the data from the fields specified but all the other existing fields in the Master Table will be empty.

If I chose for it to check for duplicates I can only ignore the current record which will not import the values for the new fields or I can tell it to replace the record in the Master which also then renders the fields that I don't want effected blank.

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Here is a screen shoot of an append operation. In this case I have already imported the excel table into my project as a new dbf table.
append.jpg

Note:
The text at the top says "If you leave a blank entry in a cell, the corresponding field in the master table will not contain data." So see the field Edittracki in the Master. The Master has everything I want already in all the records for that field. If I leave the mapping blank, the operation then renders Editteacki blank for all the records in the master. They were not blank before the operation! This occurs for all fields I leave not mapped.

I am just trying to bring in data from a few new fields to the master but then lose the data in all the other fields that I leave unmapped. I don't want to map all the other fields because the transaction table doe not have corresponding fields to those in the master.

Selecting next from the above screen shot I again get the duplicate option.
duplicate.jpg

If I select No, then I get all new records not values updated in the existing records. If I select Yes I can either have them not appended which defets the purpose or it replaces the entire record so that now only the mapped fields have data. All the others will have the values wiped out.

So this is where I get the idea that appending from one table to another blanks out the not mapped fields in the same record that the append occurred. If there is another way please do share.

Thanks.

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

The most important part I forgot wit the two options above. I do not get the opportunity to select a unique field shared between the tables with which to match the data to the correct records so those operations (Import and Append) are pretty unless.

The operation called Update does not give you the option of selecting fields from another table. With Update it seems you can only change what is in an existing table from values from withing the existing table. Not useful in this case.

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Well join does not work very well either. Besides the operation failing every time it seems it will not maintain my calculated field definitions from the Master to the new table. So I will have to recreate those if I actually got Join to work.

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Decided to match each table precisely with the same exact records. They both have exactly 8231 records and are ordered based on a unique ID. So lets go old school and just cut an paste. Doesn't work. The Paste just places the data from the Cut into new records so I go from 8231 records to 16462. AN extra 8231 useless records. Clearly I don't know how to use Alpha. Those of you more experienced would never have made this so popular if what I am trying to do were really so hard.

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Having searched the Forum and Wiki, it seem that no one in the history of Alpha has ever wanted to redesign an existing table to include some new fields and then import values from another table to populate those fields in the Master table. Amazing. Or I just don't know the correct terms for the search.

[Al Buchholz]

Re: Add new fields to existing table and populate just those fields with an import or append?

Here is a screen shoot of an append operation. In this case I have already imported the excel table into my project as a new dbf table.
[ATTACH=CONFIG]38381[/ATTACH]

Note:
The text at the top says "If you leave a blank entry in a cell, the corresponding field in the master table will not contain data." So see the field Edittracki in the Master. The Master has everything I want already in all the records for that field. If I leave the mapping blank, the operation then renders Editteacki blank for all the records in the master. They were not blank before the operation! This occurs for all fields I leave not mapped.

I am just trying to bring in data from a few new fields to the master but then lose the data in all the other fields that I leave unmapped. I don't want to map all the other fields because the transaction table doe not have corresponding fields to those in the master.

Selecting next from the above screen shot I again get the duplicate option.
[ATTACH=CONFIG]38382[/ATTACH]

If I select No, then I get all new records not values updated in the existing records. If I select Yes I can either have them not appended which defets the purpose or it replaces the entire record so that now only the mapped fields have data. All the others will have the values wiped out.

So this is where I get the idea that appending from one table to another blanks out the not mapped fields in the same record that the append occurred. If there is another way please do share.

Thanks.

Build the operation but don't use the genie. Build by opening the card to fill out the operation options.

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Solution:

Export Master alpha table to Excel. Merge that master excel table with the fields from the other excel table manually. Import the new merged table into alpha as a new dbf. Edit the structure of the new dbf to be exactly like the original master dbf. Redefine all calculated fields. Finally, rename old master table and then rename new table to the previous name of the Master table. Test that all components and reports work with the new table that now has the mane of the table they were all built around. Many many hours later wonder why you can't automate such a process. Prey that it all works.

Thanks. :-)

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Thanks I did both. Same choices different dialog boxes in the process and it was just easier to get the screen shots by using the genie to get to them rather than traverse the builder for that purpose.

This is the builder choices for an append.

All (This one will just assign the values to entirely new records)
Unique only (This one will not allow any of the new data to be added because the records are not unique)
Unique, replace existing (This one will replace all the existing data in the fields I did not map with blank data)

So Results are the same at least in my hands. I am sure this will get figured out in a few days and I will wonder why I couldn't figure it out when I needed it. :-)

[Al Buchholz]

Re: Add new fields to existing table and populate just those fields with an import or append?

All (This one will just assign the values to entirely new records)
Unique only (This one will not allow any of the new data to be added because the records are not unique)

Yes you don't want this choice.

Unique, replace existing (This one will replace all the existing data in the fields I did not map with blank data)

I don't know where you get the idea that the unmapped columns will be blanked out in existing rows. That does not happen.

The text you referred to previously is discussing what happens when a new row (record) is generated.

In new rows you must map all of the columns that you want data in, but that's not what you are doing.

In some other software that I've used prior to Alpha the terminology was add update. ie add if the key doesn't exist otherwise update the existing record.

Same thing is happening with the Alpha append choice - Unique, replace existing

[Hansolo]

Re: Add new fields to existing table and populate just those fields with an import or append?

Thanks for your response. After doing this dozens of times I can assure you that at least in my hands the data in existing fields which I don't want to change do get blanked when the fields I mapped actually get values.

However, as cryptic as it sounds there is an operator called "Post" that at least got most of this work done for me. It still required me to import the data into a new alpha table first and of course make sure field types were the same between the master and transaction table but then it had the correct logic to "replace" each of the NULL fields in the Master with Values from the fields mapped in the transaction table.
post.jpg

I would like to meet the guy that named these Operation titles and wrote the short descriptions. One operation could be called "Append Records" which is what "Append" does and another could have been called "Append Fields" which is what "Post" does. Anyway, 12 hours later.....

Now all I have to do is remember this when I need to do it again in a year or so on another project!

Thanks for the moral support Al.