Updating a Clients Data Structure and Form design

[Ron Cooper]

I have read the manual, honest, and it reads copy all files except ASX, DBF, CDX, FPT, MPX and MUF. I want to give the Client a new table structure, adding a new field, without destroying the data. I managed to change the form design but the table structure was not changed. In what file is the Data Structure stored. Have I got to copy the Clients files, modify the Data Structure and then copy back or is there a better way?

[Stan Mathews]

Re: Updating a Clients Data Structure and Form design

In the old days, you had to get their table, modify the structure, send the table back to them.

Now you can email them a script to add the fields.

The script will likely use table.add_fields()

The instructions for creating that script and how they should run it are here.

[Ron Cooper]

Re: Updating a Clients Data Structure and Form design

Stan
My Client is just around the block and knowing him it would be safer if I followed plan A (the old days). In fact I will visit and do it myself. I think this is a weakness i.e. having the data structure stored with the data, but I will bow to your superior knowledge?

[DaveM]

Re: Updating a Clients Data Structure and Form design

I suggest you copy his whole application including the fields you did not get. Do table(dbf) modifications and add the fields to it. Complete your programming chores.

Just as stan said

Make a simple script and name it xx or something, put the following code in it but be careful to rename the entries and delete after 1 run.

'add the following fields to table "cars"    If run twice, it will happen twice and the last digit of name will come out "0"
fields = <<%str%
tankgals,n,2,0
minleftpct,n,2,0
maxleftpct,n,2,0
Rearmin,n,8,3
Rearmax,n,8,3
Mindiag,n,8,3
Maxdiag,n,8,3
%str%
table.add_fields("cars", fields)

These are the files you would typically NOT copy back to the client computer.
DBF, CDX, FPT, MPX and (MUF? alpha rebuilds it if it is not there) If you change the indexes, you may want to put you new cdx in there and reindex that table.

By the way, I am doing exactly that right now for an app I sell over the internet. I have to add the above fields and 3 times that many to another table and make it downloadable off the internet.

[Ted Giles]

Re: Updating a Clients Data Structure and Form design

Ron, to save you "greef" in the future, you might want to consider an approach which purists will consider a waste, but it works for me.
All tables change over time, so I always allow a few extra 1 char fields X1, X2, X3 etc in the table.
I have started putting a function in the Housekeeping section which allows users to mail bugs and enhancements directly to me.
If you create a "Zip and Send" of the database to your good self, then changing and reinstalling should be relatively easy. Zip and send back.
Another way is to create the new empty application and run an Append operation to suck the data from the old instance to the new instance. Works as long as you know where the old instance is.
You have the right approach though. With many users they have a day job and it ain't messing with upgrades.
Have you considered something like Remote Assistance where you can dial in and fiddle?

[DaveM]

Re: Updating a Clients Data Structure and Form design

I use astrum installer. With a little work, you can copy the complete ol app to a safe place(you know where that is) and make a temporary startup adb to append the records to the new complete app that you install. The new adb is only run once and you can destroy it after the work is done.

with astrum -
before install, copy complete app to save location - may use a batch file for this if you prefer - suggest NO zip.
install
run the temp.adb - this runs an append opertaion(s) and anything else you want - once run it deletes itself something like path\alpha.exe -temp.adb
complete install
Client can then start their application as usual
Don't delete the saved application - you may need it!
Be sure you test this before deploy!


Please let us know how you did. It may help someone else.

[CALocklin]

Re: Updating a Clients Data Structure and Form design

There are many ways to do it. Since your customer is so close, going there and doing it "manually" may be the best answer. If you do that, I'd add the new fields on their current version and then install the updated data dictionary files (*.dd*, *.se*, *.al*) to complete the update. (That's assuming that you did all the updates on a separate copy of the app and just want to transfer the updates to their system.)

You might also want to check out this post. I make all my field changes - adding new fields or modifying existing fields - with some relatively simple code that runs in (or is called from) the autoexec script. The whole script can get very long as you add more and more changes to it but as you look at each individual field update section, the code itself is pretty simple. Doing it this way means that you never have to get a copy of your customer's data, you don't have to send any "extra" tables, and the user can even restore old data (as many times as they want) and the old data structure will be rebuilt to the new requirements (so it works with all the latest program revisions) as soon as the app is restarted.

Another advantage to my method is that a customer can skip as many updates as they want and then install the latest update and everything will work. And, yes, this has happened many times on applications that are used by multiple customers. It's not likely on one-customer applications but it's pretty common with generic apps. Some (not all) of the other update methods I've seen require each update to be installed sequentially.

Also, according to my tests, it's slightly faster to restructure a large table (some of my customers have tables with over 200,000 records) than to append that many records. You can also run into problems when appending data. For example, I believe you have to make sure you aren't appending calculated fields or the append will fail and I think autoincrement fields can get messed up if not handled correctly.

There is much more detail in the post at the link above but I think all that detail may scare some people off. If all you wanted to do was add one field, this is basically what it would take:

fld_list = [COLOR="#006400"]a5_get_fieldnames([/COLOR] "Agents.dbf" )
IF .not. ( "agnt_phonf"$fld_list )
	[COLOR="#006400"]a5_add_fields_to_table([/COLOR] "Agents.dbf", "agnt_phonf,C,18,0" )
END IF

But I prefer to add a bit more - (1) an initial test to see if the updates have already been done, (2) comments so I know what I did and when I did it, (3) a "datapath" to make sure the update is done on the actual data file so the routine can be run from a shadowed workstation, and (4) I like to set a "new_flds" variable that is a crlf() list and use that as a standard method because I often add more than one field at a time.

Below is a more complete version - the way I would actually do it.

The "check last table" section doesn't mean much here because only one field is being added. But take a look at the example in the link and you'll see why I do it. I've added dozens of fields over the last few years and they are all still in there. In fact, the attachment/example in that link only shows about 1/3 of the current total. A customer could install a 3 year old data backup and it would still work with all the latest changes to the application because all the required fields would get added automatically. I don't recall anyone ever going back that far but I have had to restore at least one that was 4 months old and I've had one customer who didn't update for over a year - and my updates for that app were averaging more than one a month at the time. Note that when using this method and the LAST field that was added is already there, then all the previous fields must be there also.

[COLOR="#0000CD"]'***********************************************************************************************
'Check LAST table and field updated first. If it has already been done, skip the rest.
'***********************************************************************************************[/COLOR]
fld_list = [COLOR="#008000"]a5_get_fieldnames([/COLOR] "Agents.dbf" )
IF "agnt_phonf"$fld_list	[COLOR="#0000CD"]'2008-07-03[/COLOR]
	EXIT FUNCTION
END IF
[COLOR="#0000CD"]'***********************************************************************************************
'***********************************************************************************************

'The "datapath" (path to the actual data files) is used so this can even be run from a shadowed workstation.[/COLOR]
[COLOR="#800080"]DIM datapath as C[/COLOR]
IF [COLOR="#006400"]a5.Get_Master_Path()[/COLOR] = ""
	datapath = [COLOR="#006400"]a5.Get_Path()[/COLOR] + chr(92)
ELSE
	datapath = [COLOR="#006400"]a5.Get_Master_Path()[/COLOR] + chr(92)
END IF

[COLOR="#0000CD"]'---------------- Add new fields to AGENTS table[/COLOR]
fld_list = [COLOR="#006400"]a5_get_fieldnames([/COLOR] "Agents.dbf" )
[COLOR="#0000CD"]'2008-07-03 Add agnt_phonf to Agents.[/COLOR]
IF .not. ( "agnt_phonf"$fld_list )
	new_flds = <<%list%
agnt_phonf,C,18,0
%list%
	[COLOR="#006400"]a5_add_fields_to_table([/COLOR] datapath + "Agents.dbf", new_flds ) [COLOR="#0000CD"]'MUST use actual data path here![/COLOR]
END IF
[COLOR="#0000CD"]'Each time you do an update that includes new fields being added to this table, a new IF section 
'is required and that section must come AFTER/BELOW the earlier sections. For the sake of your own  
'sanity and so you only need to run one a5_get_fieldnames() check per table, group changes for each 
'table together.[/COLOR]

Using this method to update fields, any update just consists of sending all the data dictionary files - *.dd*, *.se*, and *.al* - to the user after finishing the update on your system. This script would be part of the autoexec (or I put it in a function that is called by the autoexec) which means it would run as soon as they restarted the app and everything would be updated before the main menu opened. It's so automatic that in many cases the users won't even know that new fields were added unless you tell them.

The above code actually runs every time the app starts but once the new field(s) has been added, the "check last" section sees that and quits - and it only takes a couple milliseconds to run that check. I've been using this for 10 years and I find that it makes life much easier - especially when an app has been sold to multiple customers.

[jimxoc]

Re: Updating a Clients Data Structure and Form design

Cal,

Wow !! thanks for your time to offer such an elegant solution and one that protects itself and the user from mistakes as time goes on!!
Even though I am new here I can appreciate a beautiful solution like this!
I don't want to misdirect the thread so I won't go on ...

Jim

[compuaid]

Re: Updating a Clients Data Structure and Form design

CALocklin posted a more complete script a few years ago and I've been using it ever since. This script which I setup as a function and then call from the Autoexec script not only adds any new fields to your data structures but also includes the ability to modify field lengths and even perform data transformations. It also works to allow users to restore backup data that is in the older format and will re-apply your structural changes the first time the data is accessed. Great script!

f_updatefiles.txt

FUNCTION f_updatefields AS L ( )
'########################################################################
'########################################################################
' Update fields/tables.
' Check the most recent modification first. If it is already
' completed, skip to 'Done'. (If the most recent is done, obviously 
' everything else must have been done also.)
' == NOTE: All mods for a table should be kept together if the table was 
' modified earlier. The earlier mods need to be kept for at least a year 
' because (a) some users of generic apps may not have updated yet and 
' (b) they won't be able to restore and use old data files if they aren't 
' updated correctly.
' First changes MUST to come first within each table.
'########################################################################
'########################################################################
 
'I *ONLY* restructure the main tables on the server - never the shadowed 
'tables. Maybe it would work but I don't want to take any chances.
DIM datapath as C
IF a5.Get_Master_Path() = ""
    datapath = a5.Get_Path()+chr(92)
ELSE
    datapath = a5.Get_Master_Path()+chr(92)
END IF
 
'Check LAST table and LAST field updated first. If it has already been 
'done, skip the rest. Don't need to use "datapath" to get field names.
fld_list = a5_get_fieldnames( "bills.dbf" )
IF "Discount_Allowed"$fld_list '2009-08-19
    GOTO Updates_done
END IF
 
'You may want to add a Please Wait dialog here but adding new fields is 
'usually quite fast. It takes longer if you need to also update the values.

'Do Bills table first
'---------------- Bills table
fld_list = a5_get_fieldnames( "Bills.dbf" )
'2009-08-19 Add Discount_Allowed field.
IF .not. ("Discount_Allowed"$fld_list)
    new_flds = <<%list%
Discount_Allowed,L,1
%list%
    a5_add_fields_to_table( datapath + "Bills.dbf", new_flds )

    'Initialize the "Discount_Allowed" field as True.
    tpi = table.open("Bills")
    tpi.index_primary_put("")
    tpi.fetch_first()
    WHILE .not. tpi.fetch_eof()
        tpi.change_begin()
        tpi.Discount_allowed = .T.
        tpi.change_end()
        tpi.fetch_next()
    END WHILE
    tpi.close()
END IF

 
'---------------- Detail_Lines table
fld_list = a5_get_fieldnames( "Detail_Lines.dbf" )
'2009-08-19 Add Discount Allowed Field
IF .not. ("Discount_Allowed"$fld_list)
    new_flds = <<%list%
Discount_Allowed,L,1
%list%
    a5_add_fields_to_table( datapath + "Detail_Lines.dbf", new_flds )

    'Initialize the "Discount_Allowed" field as True.
    tpi = table.open("Detail_Lines")
    tpi.index_primary_put("")
    tpi.fetch_first()
    WHILE .not. tpi.fetch_eof()
        tpi.change_begin()
        tpi.Discount_allowed = .T.
        tpi.change_end()
        tpi.fetch_next()
    END WHILE
    tpi.close()
END IF
 
'Add next update to Details_lines here


'******* Examples of other types of updates that can be *******
'******* done if necessary.                             *******
 
'---------------- Order_Statusw
'2007-04-13 Change width of field in order_statusw.
'tps = table.open("order_statusw")
'fld = tps.field_get( "Stat_Typef" )
'fwid = fld.width_get()
'tps.close()
'IF fwid = 8
'    Old_list = <<%list%
'Stat_Typef,C,8,0
'%list%
'    New_list = <<%list%
'Stat_Typef,C,13,0
'%list%
'    a5_changefieldsize( datapath + "order_statusw.dbf", old_list, new_list )
'END IF
 
 
''---------------- Program_Options
'IF .not. ("Foto_Pathf"$flist) '2007-08-16
'    new_list = <<%list%
'Foto_Pathf,C,60,0
'%list%
'    a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
'    tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
'    tpp.change_begin()
'    tpp.Foto_Pathf = datapath + "Photos" + chr(92)
'    tpp.change_end()
'    tpp.close()
'END IF
 
'IF .not. ("daylightf"$flist) '2007-12-06
'    new_list = <<%list%
'time_zonef,c,8,0
'daylightf,L,1,0
'%list%
'    a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
'    tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
'    tpp.change_begin()
'    tpp.time_zonef = "Eastern" 'Eastern,Central,Mountain,Pacific
'    tpp.daylightf = .F.
'    tpp.change_end()
'    tpp.close()
'END IF
 
''2008-07-03 Added just one field so didn't use the crlf() list.
'IF .not. ( "spec_msgf"$flist )
'    a5_add_fields_to_table( datapath + "program_options.dbf", "spec_msgf,C,254,0" )
'END IF
 
'End of adding fields
'*****************************************************************************
'*****************************************************************************
'*****************************************************************************
'---------------
Updates_done:
'---------------
 
''A routine to change field sizes. This doesn't have to be embedded in the 
''previous "add fields" routines but it can.
''---- AGAIN - LEAVE FOR AT LEAST A YEAR so user can restore older backups if necessary.
''---- 2008-01-23 Update width of Sign_locf field.
''2008-07-03 Change to check field width by using a lookup - much faster 
''that way! (Much faster in "computer time" - users probably won't notice 
''the extra few milliseconds.)
'sloc = lookupc( "F", 1, "sign_locf", datapath + "Ord_Item.dbf", "" )
'fwid1 = len( sloc )
'IF fwid1 <> 11
'    'Change width of Sign_locf field in Ord_Item 2008-01-23.
'    tpx = table.open( datapath + "Ord_Item.dbf" )
'    fld1 = tpx.field_get( "Sign_locf" )
'    fwid1 = fld1.width_get()
'    tpx.close()
'    IF fwid1 <> 11
'        Old_list = "Sign_locf,C," + ltrim(str(fwid1)) + ",0"
'        New_list = "Sign_locf,C,11,0"
'        a5_changefieldsize( datapath + "Ord_item.dbf", old_list, new_list )
'    END IF
'END IF
 
'*****************************************************************************
'*****************************************************************************

END FUNCTION

[CALocklin]

Re: Updating a Clients Data Structure and Form design

Good to know someone is using it. We (anyone who posts these things) get very little feedback on things like that.

I don't have the time right now but this question comes up so often that I should probably go find the post with the best detailed description and store it somewhere so I can just link to it the next time someone asks. Since all these examples are revisions (and/or combinations) of existing functions, it takes way too much time to keep repeating myself. This time I decided to make it simpler so it would be less confusing for the reader and less time for me. In fact, maybe I should save both links - i.e., "Here is in a simple example to cover the basic 'just add a field' situation and here's a more complex version of the same thing showing some other things that can be done."

[JetLi]

Re: Updating a Clients Data Structure and Form design

Good to know someone is using it. We (anyone who posts these things) get very little feedback on things like that.

I don't have the time right now but this question comes up so often that I should probably go find the post with the best detailed description and store it somewhere so I can just link to it the next time someone asks. Since all these examples are revisions (and/or combinations) of existing functions, it takes way too much time to keep repeating myself. This time I decided to make it simpler so it would be less confusing for the reader and less time for me. In fact, maybe I should save both links - i.e., "Here is in a simple example to cover the basic 'just add a field' situation and here's a more complex version of the same thing showing some other things that can be done."

I cannot find the link yet, maybe moderators will find the best link for this, it is one of the most important aspect when a change is made in the table structures and forms, i tried doing it but many forms don't work.Thanks!

[preston2]

Re: Updating a Clients Data Structure and Form design

Good to know someone is using it. We (anyone who posts these things) get very little feedback on things like that.

Just used it and it worked perfectly. Thanks Cal.