How to freeze past invoices

[gaby_h]

I have a standard Invoice--InvLineItems set with its data entry form. I need to freeze past invoices, ei implement the following restrictions:
for any invoice prior the year end date (say, Dec 31 2007), the user
- can't delete the invoice
- can't modify the invoice
- can't modify any line item
- can't delete any line item
- can't add a line item to the invoice

Any pointer will be appreciated

[Stan Mathews]

Re: How to freeze past invoices

Where is the date stored?

You can code the CanSaveRecord and CanDeleteRecord in a table's field rules.

if year(somedatefield) < year(date())
'message box to explain why no changes allowed if desired
cancel()
end if

You could also have the onfetch event for a form check the date of the invoice and set the fields to read only if the date is in the previous year.

[gaby_h]

Re: How to freeze past invoices

Where is the date stored?

The Date is stored in Invoice header

[gaby_h]

Re: How to freeze past invoices

Could someone please review this script that have compiled by combining examples from the user guide. Please don't laugh, i am barely starting to understand Xbasic.
My form's name is MainForm, the embedded browse is Browse1. I placed the script in the OnFetch event.

SELECT
CASE Year(MainForm.Inv_Date) < 2008
:MainForm.restrict_change=.t.
:MainForm.restrict_enter=.t.
:MainForm.restrict_delete=.t.
browse1.browse.readonly=.t.
CASE Year(mainform.Inv_Date) = 2008
:MainForm.restrict_change=.f.
:MainForm.restrict_enter=.f.
:MainForm.restrict_delete=.f.
browse1.browse.readonly=.f.
END SELECT


it seems to work..., but need an expert opinion

[Bob Houle]

Re: How to freeze past invoices

What's going to happen next year? ;)

Seriously...

We use invoices at our shop.

Each invoice form has a "completed date" field that is normally blank until the invoice is paid.

Each time you open the form for viewing, it checks to see if the field is not blank.

If the invoice is completed, you require a password to edit it's contents.


Works very well and it's not time sensitive.

--Bob

[gaby_h]

Re: How to freeze past invoices

Hi Bob:
We are nearly neighbours, i am in Vancouver.
We are a travel agency; unlike most businesses every invoice has 2 sides: customer side (A/R, which changes only exceptionally) and a vendor side (A/P: commission, GST/commission which change often). These figures become definite when we receive the commission from the vendor and often times the original figures have to be corrected (the vendor decided to send less or more than originally stated, sometimes they never send the commission, especially on hotel bookings), sometimes the commission is sent months after the client has traveled. All this has to be corrected on the 2nd side of the invoice; the corrections are made as we go. However once the accounts have been audited, I need to freeze all past invoices.

I need someone to review and streamline my script.

[MikeC]

Re: How to freeze past invoices

Gaby,
By your script it looks like you want this done automatically....but how can you seeing as how there is no set criteria in which to establish when a "freeze" should occur?? Unless I'm missing something.... :) Maybe the value of when the auditing is done could be used to set a logical field or your restrictions like the following.

First thought is to have a logical field that can be set (say via a password protected button that would toggle the value or restrictions) whenever you or the Admin deemed appropriate.

very similar to what Bob proposed--just iterated a bit differently is all.

[gaby_h]

Re: How to freeze past invoices

Gaby,
Maybe the value of when the auditing is done could be used to set a logical field or your restrictions like the following.
l.

That's right. It is when the audit is completed and figures are corrected (usually 80-90 days after the year end). Does not need to be done automatically.
Typical scenario: around March 20-25 2008 when audit is completed and we need to freeze past invoices, I go back to the script and change the year from 2006 to 2007. Do the same thing the following year in March 2009 (manually change year in script from 2007 to 2008), etc...

My main concern is if my script is programatically correct.

[MikeC]

Re: How to freeze past invoices

Gaby,
The script appears to be fine (not saying I am one of the experts you were hoping to get an answer from!).

As a side note-- I started down the road you are using, but soon changed my mind as I ended up wanting my entire database except for reporting to be basically frozen in regard to change.

I used a global variable called gvRestrict_Archive set with an autoexec script (Alpha plays this script automatically every time it opens unless set to not do this). I then placed a script on the CanSaveRecord of the field events for all the tables I wanted to enforce this on.

if gvRestrict_Archive=.T. then
   ui_msg_box("Attention!","You cannot Change or Enter data when viewing an Archive file.",UI_ATTENTION_SYMBOL+UI_OK)
   cancel()
   end
end if

When set to True nothing can be saved.

It sounds like you could do similar on the CanSave event of a form if you wanted or needed an alternative later on.

[gaby_h]

Re: How to freeze past invoices

Mike: sounds a very good approach. I'll study it and try to implement it. Thanks for sharing

[martinwcole]

Re: How to freeze past invoices

I think you may have to say CASE Year(MainForm:Inv_Date.value) < 2008

when referring to layout objects, you use value
when referring to table field values
t=table.current()
case t.first_name="Martin"

first_name.value refers to the current value in the form whether it is saved on still in change mode

t.first_name refers to the current saved value in the table, even when the form's unsaved value is different

[MikeC]

Re: How to freeze past invoices

Although most times you would be correct Martin, the expression builder disagrees and it should simply be this when it is a field on a form:

CASE Year(Inv_Date) < 2008

if it is the parent object/field. If a child then the child table should preface it -- if child was say "Chld" then it would be:

CASE Year(Chld->Inv_Date) < 2008

I would have to think about it some to say why---I usually let Alpha decide such things by letting the expression builder or genies determine it!! :)

[Bob Houle]

Re: How to freeze past invoices

However once the accounts have been audited, I need to freeze all past invoices.

Gabe:

I'd do the same thing as what I said previously...

Create a field called: Audit_Date (added to you Invoice_Header table)

Normally the field will be empty until you've gone through the audit process, at which time you enter the correct date. (Can be done with a button and script, whenever needed)

Once the "Audit_Date" field is filled, the Invoice form is locked with the appropriate code.

email me and I'll help you out if req'd.

--Bob