My invoices are changing!

[DVSOL005]

Re: My invoices are changing!

Hi Carol,

I had what I think is a similar issue with an invoice application I created for a client early last year.
Things were changing and I couldn't put my finger on the cause of it.
After observing through the default browse of both header and detail table I found that records were changed and phantom records existed in the detail table. After some time and building frustration I decided to take following steps.
1. Limit the invoice form to only allowed to create new invoices, no going back to previous invoices!
2. Observe what client was doing when creating a new invoice.
3. As I needed to check the invoices produced, I created a report of all invoices with header and detail lines and made calculations for the appropriate totals. As the invoice total is stored in the header record, the calculated invoice total on the report (total of all detail records) can be compared against the invoice total in the header and the differences are indicated on the report.
4. Through a report menu the client could view/check previously created invoices and there are different forms that provide details about previous invoices too.

In the end I found out that under rare circumstances a phantom record could be created in the detail table. That sort of messed up the indexes I think. I created an operation to check for and remove phantom records.
This operation is running when a new invoice is saved.
Since then the client never had a single problem while he is creating invoices daily. I have never been able to fully explain the differences indicated on the report but that didn't bother me, both the client and I were convinced that the problem was solved.

I am just trying to provide an alternative way of looking at the issue at hand, not sure it works for you.

Regards,

[gmeredith17]

Re: My invoices are changing!

the product would have been a huge failure,

There are some that do believe V9 was a bug ridden failure. Even Alpha gave up trying to fix the bugs 6 or 7 months before V10 was released.

However, in this case I suspect it is not a bug and probably something to do with how the db has been structured. The recommendation for posting a sample is a good one as I'm sure one of the Alpha Gurus will be able to help solve the issue.

[rtbtat]

Re: My invoices are changing!

gmeredith17, I don't share your opinion that ver 9 was a "bug ridden failure", but I do agree that she should post her app so she can receive help on this forum.

Having said that, she implied everything in her database worked properly for who knows how long, but the index corruption issue started when she switched to ver 9. In essence, if her application design was the root cause of index corruption, why didn't it happen when she was using ver 4?

In light of that, my point was very simple. I wasn't claiming ver 9 was bug free because there probably is no software I know of on the market that is bug free. I was stating that if ver 9 regularly corrupted indexes, as she described her problem as a post upgrade one, it would have been a huge failure.

[gmeredith17]

Re: My invoices are changing!

gmeredith17, I don't share your opinion that ver 9 was a "bug ridden failure", but I do agree that she should post her app so she can receive help on this forum.

I respect your opinion and I'm glad it is working for you.

In essence, if her application design was the root cause of index corruption, why didn't it happen when she was using ver 4

Although I bought version 7 I only really started using Alpha5 with version 8 so have no experience with V4. However, from other threads I gather a lot has changed over the versions and it isn't unusual for things that worked in a previous version not to work quite the same in another. Tweaking here and there is required and I would think expected by most. There have been a number of threads regarding action scripting changing across the versions. Working in one version and not the same in another. I don't use action scripting often and generally its just to get the basics of doing something and then I modify in xbasic.

I guess now it is down to Carol to let us know how she is getting on.

[MikeC]

Re: My invoices are changing!

And I think the guessers are getting pretty guessed out by now. Unusual things happen and sometimes coincidentally at the same time as when other things change (such as version upgrade). Not saying it is or is not an issue with v9....no way to tell really without a reproducible sample to work with. A very recent thread just ended with a very similar situation in that Alpha was being blamed....but what actually was happening was that certain users were entering in data in an unusual way which caused the problems.....

And guess what? The thread was well over 30 posts. The sample was finally given at post 20. If it had been given sooner much of the time guessing would not have had to be spent.

(PS--if noticed, you will see that I much more rarely get involved with threads until a sample is present than when not!! I just am not that intuitive to figure these things out I guess. :) )

[cbauer50]

Re: My invoices are changing!

Thanks, Bob! I think that was the problem. I noticed that my Customer ID width was 8 in one table and 5 in another - so changed them both to 8.

The other thing was when I was checking the fields on my forms, the numbers in the width in the Display Format area were HUGE, i.e., for the customer ID, instead of 8, it would have huge numbers - like in the thousands - like 59876 - I have no idea how they could have gotten numbers in there like that, except that it had to have happened when I first converted my v.4 database to v.9. Here is a screen shot of what I'm talking about. I went into each of my forms, reports, etc. and changed them all to match the width in the tables.

I have been checking my invoices for the past 3 days and they all seem to be fine now. The numbers (subtotal, totals, amount received, etc) aren't changing to like they were.

Carol

Carol,

One thing to check is your key fields between your master and child tables. They seem to need to be the same length in V9. it didn't seem to matter in earlier versions. I'm struggling through an upgrade too.

Bob

[CALocklin]

Re: My invoices are changing!

The other thing was when I was checking the fields on my forms, the numbers in the width in the Display Format area were HUGE, i.e., for the customer ID, instead of 8, it would have huge numbers - like in the thousands - like 59876 - ...

FWIW:
I don't think that should be an issue. (Although it might be indicative of some other problem that was created during the change to v9.)

One of my pet peeves is that A5 sets the field width to the width of the field but there appears to be almost no reason* for that. This only becomes a problem when the field width is increased after a form/report has been created. Then I have to go find every form/report with that field and increase the width - an irritating waste of my time since there is virtually no reason for the smaller field size to start with. As a result, I'm planning another "AIMS developer addon" that will change the width of all field objects on a form/report(s?) to at least 255 so that I never have to increase one manually again. As it is, any time I'm editing something on the Format tab of the field properties dialog, I always stick an additional "1" or "2" on the left of the field width. So, assuming the actual field width was 20, the width specified for the field object becomes either 120 or 220 so I never have to worry about it if I change the basic field width in the future. This has no affect on how many characters the user can enter - they can never enter more than the number allowed by the underlying field.


* There is one sorto' reason for specifying the width of the field object to be the same as the field itself - except it doesn't work right which is why I can only say "sort of". When you right click on a field object on a layout, there is an option to "size to fit". However, A5's idea of "size to fit" is usually more like "size to fit - times 2". Here's a video to show what I mean.

[Bob F]

Re: My invoices are changing!

Thanks, Bob! I think that was the problem. I noticed that my Customer ID width was 8 in one table and 5 in another - so changed them both to 8.

The other thing was when I was checking the fields on my forms, the numbers in the width in the Display Format area were HUGE, i.e., for the customer ID, instead of 8, it would have huge numbers - like in the thousands - like 59876 - I have no idea how they could have gotten numbers in there like that, except that it had to have happened when I first converted my v.4 database to v.9.

Carol,

Glad to help. I went back and looked at a few of my forms fields lengths but didn�t find any out of whack. I�ve done a few more upgrades since V4 than you , V7 and V5 I think, so maybe they were fixed somewhere along the line. The good news is that my application is settling in and I�ve put the jar of Rolaids back in my drawer.

Good Luck,
Bob F.

[rtbtat]

Re: My invoices are changing!

Changing the size of the linking fields to match is probably a good idea. However, coming to the conclusion that your problem was resolved based upon that modification might not be correct. Your application may be working properly because there was no external problem, such as a brief power outage while A5 was open, that once again corrupted the indexes, causing the problems you described.

I have 2 computers, one of them does NOT have a UPS battery backup. In my part of the country, the power goes out for approximately one second about twice a year and that forces an improper computer shut down. When that happens, I must of course reboot the machine. In general, I cannot possibly remember if a specific A5 application was open when the power went out. If it was, the next time I open the application the indexes might be corrupted.

Now that your application is functioning correctly in ver 9, that may indicate your corrupt index issue was not a result of a design flaw in version 9.

[cbauer50]

Re: My invoices are changing!

Well, I'm back. I went to open my database today to enter some invoices. When I tried to add a product (id, qty, etc.) and then tried to save it after everything was entered that needed to be entered, the product list kept popping up again and wouldn't let me save it.

I then went to look at the last few invoices, and sure enough, same problem I had before. Amounts I entered for amount received, s/h, etc., were all screwed up.

I then packed the tables and all was well again.

I can tell you that I have had no power outages, the computer was shut down properly, etc. I maybe open the database once a day or every other day and always close it as soon as I am finished entering invoices. So I know for a fact that these things are not the problem.

I thought the problem was fixed, but I guess it's not.

Carol

Changing the size of the linking fields to match is probably a good idea. However, coming to the conclusion that your problem was resolved based upon that modification might not be correct. Your application may be working properly because there was no external problem, such as a brief power outage while A5 was open, that once again corrupted the indexes, causing the problems you described.

I have 2 computers, one of them does NOT have a UPS battery backup. In my part of the country, the power goes out for approximately one second about twice a year and that forces an improper computer shut down. When that happens, I must of course reboot the machine. In general, I cannot possibly remember if a specific A5 application was open when the power went out. If it was, the next time I open the application the indexes might be corrupted.

Now that your application is functioning correctly in ver 9, that may indicate your corrupt index issue was not a result of a design flaw in version 9.

[Tim Sutherland]

Re: My invoices are changing!

Carol,

I don't think anyone here is going to be able to diagnose your problem without looking at your application in depth.

I can tell you though, that I had similar oddities several years ago in my invoicing script in my application.

I was able to solve it by adding a routine to the autoexec script that rebuilds the indexes the first time the application is run on any given day.

I use Cal Locklin's create index rebuild utility that not only rebuilds the indexes, it repairs them if they become corrupt or lost.

If packing the tables works for you, you might consider something similar with running the packing tables utility once a day.

Good luck...
Tim

[CALocklin]

Re: My invoices are changing!

Carol, is there any chance that a lot of invoice records or invoice item records are being deleted? I've seen some very strange things happen when either the last record is deleted or a large percentage of the existing records are deleted.

Usually the "last record" issue is just when viewing a large number of records in a browse and using the Page Up/Down keys - it jumps around and you have a hard time getting to anything other than the first or last screen.

The "large percentage delete" issue is usually stranger and sounds a bit like what you are describing - records that seem to have changed, can't be found, etc. However, I can't imagine why anyone would generally be deleting a lot of invoice information. (FWIW, one customer recently had about 350 deleted records out of a total of about 800 records and had these problems - packing solved it but rebuilding indexes did not.)

Here's a generic pack routine that should work on any application that has all data files in the main application folder. It should probably be changed to a function and given a "run silent" option but I haven't done that yet so it is what it is for now:

'Date Created: 11-Sep-2006 05:36:18 PM
'Last Updated: 05-Jul-2008 01:34:43 PM
'Created By  : Cal Locklin
'Updated By  : Cal Locklin
'----------------------------------------------------------
' THIS SCRIPT DESIGNED TO PACK ALL TABLES USING table.enum
' SO IT IS NOT NECESSARY TO CHANGE THE SCRIPT IF TABLES
' ARE ADDED OR DELETED.
'----------------------------------------------------------
' BY PACKING ONLY THOSE TABLES THAT NEED PACKING, MUCH 
' TIME IS SAVED.
'----------------------------------------------------------
'----------------------------------------------------------
' NOTE: Tables that are packed will also get the indexes updated. This is what 
'wastes time when packing tables that don't need to be packed.
'----------------------------------------------------------
OPTION NOBREAK

msg = "Packing tables will PERMANENTLY remove all deleted records from the database." +crlf(2)
msg = msg + "When records are deleted from the table, they are removed from the 'active' "
msg = msg + "record list and are no longer available for viewing, editing, or reporting.  "
msg = msg + "However, the actual data is still there and can be recovered if necessary. " +crlf(2)
msg = msg + "Packing the table will permanently remove these deleted records.  This means "
msg = msg + "they can no longer be recovered but it also means smaller table sizes which "
msg = msg + "results in faster sorting and queries.  Packing also improves index "
msg = msg + "reliability in some cases." +crlf(2)
msg = msg + "Be sure you have a recent backup before packing the tables.  If you don't "
msg = msg + "have a recent backup, click 'No' and make a backup first." +crlf(2)
msg = msg + "START PACKING NOW?"
resp = ui_msg_box( "Pack  ALL  tables?", msg, 32+ui_yes_no+256 )

IF resp = ui_no_selected
    END'
END IF

resp = ui_msg_box( "PROMPT FIRST?", "Prompt before packing each table?", 32+ui_yes_no )
IF resp = ui_yes_selected
    askfirst = .T.
ELSE
    askfirst = .F.
END IF 

qtypkd = 0
qtyfnd = 0

statusbar.Show()
IF askfirst
    g_title = "Checking for tables to pack."+crlf(2)+"(See bar at lower left for status)"
ELSE
    g_title = "Packing All Tables"+crlf(2)+"(See bar at lower left for status)"
END IF
PleaseWait_fnc(.T.,g_title)

tbl_list = :A5.table_enum()
tbl_list = sortsubstr( tbl_list, crlf() )    'Not req'd - just allows the user to judge progress 'alphabetically'.
tcnt = w_count( tbl_list, crlf() )
FOR qx = 1 to tcnt
    tbl_name = word( tbl_list, qx, crlf() )
    packres = packit( tbl_name + ".dbf", askfirst )
    IF packres = "F"
        qtyfnd = qtyfnd + 1
    ELSEIF packres = "P"
        qtyfnd = qtyfnd + 1
        qtypkd = qtypkd + 1
    ELSE
        'FYI: packres = "N" so do nothing.
    END IF
    statusbar.robot()
NEXT

PleaseWait_fnc(.f.,"")
statusbar.clear()

IF qtyfnd  = 0
    msg = "No tables needed to be packed."
    'msg = "No tables with deleted records were found."
    ui_msg_box( "ACTION COMPLETE", msg, ui_information_symbol )
ELSE
    msg = ltrim(str(qtyfnd)) + " tables were found containing deleted records." +crlf(2)
    msg = msg + ltrim(str(qtypkd)) + " tables were packed."
    ui_msg_box( "ACTION COMPLETE", msg, ui_information_symbol )
END IF

END'

FUNCTION packit as C( tblname as c, ask=.T. as L )
'Returns N if no deleted records were found, P if it was packed and F if found but not packed.
    packit = "N"
    ON ERROR goto Not_exclusive
    tp = table.open( tblname, file_rw_exclusive )
    ON ERROR goto 0
    StatusBar.Set_Text("Checking " + tblname + " for deleted records.")
    hourglass_cursor(.T.)
    reccnt = tp.records_deleted()
    tblname = left( tblname, at( ".dbf", tblname )-1 )
    tblname = upper( tblname )
    IF reccnt >= 1
        packit ="F"
        resp = ui_yes_selected
        IF ask
            msg = "Permanently remove the " + ltrim(str(reccnt)) + " records?"
            resp = ui_msg_box( "PACK THE " + tblname + " TABLE?", msg, ui_question_symbol+ui_yes_no )
        END IF
        IF resp = ui_yes_selected
            packit = "P"
            tp.pack()
        END IF
    END IF
    hourglass_cursor(.F.)
    StatusBar.clear()
    tp.close()
    EXIT FUNCTION

    Not_exclusive:
    '*** SINCE THIS IS RUN FROM THE "Menus" TABLE, THE "Menus" TABLE CANNOT BE UPDATED HERE.
    '*** AT THE SAME TIME, THERE IS LITTLE, IF ANY, REASON TO HAVE INDEXES IN THE Menu TABLE.
    IF left( tblname, 4 ) <> "Menu" .and. left( tblname, 2 ) <> "M_"
        ui_msg_box( "*** WARNING ***", "The '" + upper( tblname ) + "' was not able to be packed.  Someone is probably still using this table.", 48 )
    END IF
END FUNCTION

'NOTE: This is an older, much 'lower powered' version of the PleaseWait_fnc.
FUNCTION PleaseWait_fnc as C (Show=.F. as L, info_msg="" as C)
    'Description:Shows "Please Wait" Xdialog with optional sub-message.  Run function with Show=.T. to show the message. Use no arguments (or .F.) to turn off the message.
    PleaseWait = ""    'Just so it can return something.

    IF show = .F.
        IF ui_modeless_dlg_exist("Processing")
            ui_modeless_dlg_close("Processing")
        END IF
        EXIT FUNCTION
    END IF
    mlen = len(trim(info_msg))
    'Skip the first line if the message is shorter than 45 characters.
    IF mlen < 28
        'If less than 28, we will use a fixed font to center the text.
        info_msg = crlf() + space((28 - mlen) / 2) + info_msg
    ELSEIF mlen < 45
        info_msg = crlf() + info_msg
    END IF

    wait_txt = "Please Wait"
    box_code = <<%dlg%
{can_exit=closeit}
{background=Dirty White}
{font=arial,16,bi}
{sp=9}{text=35,2:wait_txt};
{line=1,0};
{condition=(mlen<28)}
{region}
{font=Courier,10,n}{text=45,4:Info_msg};
{endregion}
{condition=(mlen>=28)}
{start_pos}
{region}
{font=Courier,10,n}{text=45,4:Info_msg};
{endregion};
%dlg%
    box_event = <<%code%
IF a_dlg_button = "Closeit"
    close_result = Close_warning()
    IF close_result = "Yes"
        ui_modeless_dlg_close("Processing")
    END IF
END IF
%code%

    ui_modeless_dlg_box("Processing", box_code, box_event)
    ui_modeless_dlg_setfocus("Processing")
    ui_modeless_dlg_refresh("Processing")

END FUNCTION

FUNCTION Close_warning as C()
    msg = "You have attempted to close the 'Please Wait' message manually. This should not be done "
    msg = msg + "unless the program has stopped running."
    hdr1 = msg
    msg = "PLEASE VERIFY THAT THE PROGRAM HAS STOPPED BY CHECKING THE FOLLOWING:"
    hdr2 = msg
    msg = "The status bar at the lower left corner of the database window has stopped changing."
    msg1 = msg
    msg = "The hard drive light has gone off and/or any writes to the hard drive should have "
    msg = msg + "been finished long ago."
    msg2 = msg
    msg = "ARE YOU SURE YOU WANT TO CLOSE THIS 'Please Wait' MESSAGE?"
    msg3 = msg

    DIM dlg_result as C
    dlg_result = ui_dlg_box("*** WARNING ***", <<%dlg%
{ysize=.3}{sp};
{units=F}
{text=105,2:hdr1};{sp};
{font=MS Sans Serif,8,b}{text=90,1:hdr2}{font=MS Sans Serif,8,n};{sp};
{region}
  - |{text=100,1:msg1};
  - |{text=100,1:msg2};
{endregion};{sp};
{justify=center}{font=MS Sans Serif,8,b}{text=69,2:msg3}{font=MS Sans Serif,8,n};
{justify=center}<*15Yes> <15No>
%dlg%,<<%code%
%code%)

    Close_warning = dlg_result

END FUNCTION

[cbauer50]

Re: My invoices are changing!

No, I haven't deleted any invoices. The only thing I have done with the invoices was search that field for a certain invoice (by inv. #).

When I open the invoice form, it opens showing the very first invoice. I click on the "Z-A" icon to go to the last invoice and enter the new invoice.

Other than that, I have not deleted any invoices, customers, products, etc.

Packing solves the problem, but it just makes me nervous as to why the past few invoices have changed.

Confusing as to why this is happening.

Carol


[QUOTE=CALocklin;524253]Carol, is there any chance that a lot of invoice records or invoice item records are being deleted? I've seen some very strange things happen when either the last record is deleted or a large percentage of the existing records are deleted.

Usually the "last record" issue is just when viewing a large number of records in a browse and using the Page Up/Down keys - it jumps around and you have a hard time getting to anything other than the first or last screen.

The "large percentage delete" issue is usually stranger and sounds a bit like what you are describing - records that seem to have changed, can't be found, etc. However, I can't imagine why anyone would generally be deleting a lot of invoice information. (FWIW, one customer recently had about 350 deleted records out of a total of about 800 records and had these problems - packing solved it but rebuilding indexes did not.)

[Stan Mathews]

Re: My invoices are changing!

I click on the "Z-A" icon to go to the last invoice and enter the new invoice.

Not likely to be a problem but completely unnecessary. If you begin a new record while viewing the first record, the new record will be added as the last record. If you want to view the last record prior to beginning a new record, fine, but viewing it or not has nothing to do with beginning a new record.

IOW you don't need to find the previous last record in order to add the next record after it.

[cbauer50]

Re: My invoices are changing!

I do realize that, but because I don't trust the program at this point, I have gotten in the habit to check the last few invoices I've entered to make sure they haven't changed.

Carol

Not likely to be a problem but completely unnecessary. If you begin a new record while viewing the first record, the new record will be added as the last record. If you want to view the last record prior to beginning a new record, fine, but viewing it or not has nothing to do with beginning a new record.

IOW you don't need to find the previous last record in order to add the next record after it.