Best practice for invoice status

[DeathRobot]

The invoice portion of my solution has a status field which can be one of three values; draft (created but not sent), open (sent, and waiting for payment), and paid. Is it best practice to have this field:

1) a calculation based on whether or not it's been printed plus its outstanding balance
2) a text field that changes via script (the print script changes it to open, the receive payment script changes it to paid)
3) or a text field that is manually set

Just curious if one method is by far more standard than the others.

Thanks,
Michael

[Wim Decorte]

#2

 

Don't make it a calc; it will be unstored by nature and that will make searching and reporting on it potentially painful.

Also: typically there are a fair number of validation checks / business rules that need to be checked off before the status changes, you do not want to bury that in a field definition, but do those checks as part of a scripted procedure.

[DeathRobot]

Thanks, Wim. Makes sense.

 

Michael

[comment]

Best practice here depends on the scale of your solution.

Ideally, the status would be calculated from existing data and not replicate it. This ideal is not always practical due to performance costs. In such case you need to denormalize your solution and store the same information in more than one place. This requires careful design in order to avoid possible discrepancies - e.g. payment was recorded, but the script updating the status failed to run, resulting in conflicting data being stored by the database.
 

[Wim Decorte]

This requires careful design in order to avoid possible discrepancies - e.g. payment was recorded, but the script updating the status failed to run, resulting in conflicting data being stored by the database.

 

 

Indeed, the updating needs to be transactional so that everything works or nothing.  See www.modularfilemaker.org for an implementation of transactions in FM

[DeathRobot]

Thanks, comment. This is an in-house solution in which I will be the primary user, so will be able to catch any errant discrepancies (which should theoretically be close to none). I suppose I could also create a flag for any invoices where the balance due is $0.00 but the status is not "Paid", and deal with those manually.

 

Michael

[DeathRobot]

Wim, I just downloaded this:

 

http://www.modularfilemaker.org/2013/04/transactions/

 

May take me awhile to get my head around it. Thanks for the tip!

 

Michael

[comment]

Well, the point is that you should not denormalize your solution unless you have to.

[DeathRobot]

Comment, understood. Thanks.

[Fitch]

I don't know -- I think at this point I would say that for accounting systems, de-normalization is best practice.

[comment]

I don't know -- I think at this point I would say that for accounting systems, de-normalization is best practice.

 

What is an "accounting system"?

[DeathRobot]

One more question about this. I really like the whole idea of transactions (even if I can only implement a basic version at this point). So I rewrote my script using a layout with portals, with the idea that (as per Geist) adding related records that way allows you to revert the master record and undo all the changes to the children, including creation of child records. As a test, I ran the script via the debugger and canceled it right in the middle of a loop. In this case (which could mimic a crash, or loss of network) the newly created children were not undone, but were still there. Is there a way to prevent this?

 

Michael

[comment]

Well, of course they are still "there". Cancelling the script that creates them does not undo what has been done so far. Nor does it simulate a crash or loss of network (I don't know what does). The crucial point here is that they are only half-way "there" - that is, they are not yet committed. So in theory at least, they should go up in a puff of smoke in case of a crash or loss of network. I have never tested this theory myself, but certainly, if your script encounters an error, and it is programmed to revert the record in such event, all the related records created so far will disappear without a trace except a gap in the serial number sequence (if you have set it to generate on creation).

[DeathRobot]

Perfect. Thanks, Comment. My script is set up to revert when an error is encountered, so should be good to go.