8 Replies Latest reply on Aug 8, 2012 1:47 PM by bumper

    invoice solution question


      invoice solution question



      I am creating an invoice solution and am trying to work out the best way to go around it. 

      I have four tables - Customers, Invoices, Line items and Products.


      They are related by using primary and foreign keys as suggested by the Database pros (mark Osborne) video tutorials.


      I want the user to be able to create a new customer from the "new customer" layout. There are a couple of buttons on this layout to perform the script "new order" or "new quote"


      What I am having trouble with is:


      When a quote is created, I would like the option for the user to create an order from this quote  When the order is created, I would like an order number to be created as well.

      THe order/quote fields must be editable.

      When the order is converted to an invoice, I do not want the user to be able to edit it. 

      Can I use the same layout for quote/order/invoice? I would like the layout's background colour to change with each stage of the order process...



      I hope this makes sense!

      Help appreciated....

        • 1. Re: invoice solution question

          There are a couple of ways to go about this, what I think you are saying is that you want to keep all of your data within the one table called invoices, and use it for both orders and invoices. Some systems use two tables one for orders and another for invoices. Both ways work, it just depends on your business rules, reporting needs, etc. In the first case there are two ways to prevent changes after the order becomes an invoice. One would be to create a duplicate layout and uncheck the ability to enter the fields in the browse mode, the more common and in my opinion safer in the long run is to lock the record. This is done with a special privilege set in Security where you make a field that when set to a 1 allows changes, but when set to 0 prevents alteration of the record. Search this Forum for Record Locking for the details. Note this only works when the user is into the file while using the special privilege set, full access goes right by it. Also you will need to set up a lock on the line items portal and to prevent new line items from being entered after the record is locked, turn off the creation of new records through the relationship and use a script instead that will trap for the record lock before allowing a new line item to be created.

          This really isn't as complicated as it sounds, but it usually takes one beyond the newbie stage, but if you have already done JMO's videos you won't have any trouble, just make sure to practice on a copy of your working file.

          • 2. Re: invoice solution question

            Hi Bumper - 

            that seems like a good idea - I had tried the duplcated layout originally but wasnt happy with it - privelaged sets seem more logical and simpler - I shall give it a go today...Thank yiu..

            • 3. Re: invoice solution question

              See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

              • 4. Re: invoice solution question

                Hi Phil

                I know how to lock a record - but what I would like to do is lock certain fields on a record under the invoice layout. So once a dropdown menu on the Status field is changed to Invoice, I would like all the fields relating to the invoice (date, amount serial number etc) and the portal that contains my lines (products) to be locked from any alterations by the user... Any suggestions? Thank You


                • 5. Re: invoice solution question

                  That all fits with my suggestion and it's also what Bumper suggested if I followed his post correctly. I just pointed out where you can find some decent info on how to set this up in FileMaker help.

                  Record Level Access control set up in Manage | Security like this controls access to an entire record, so this will lock all fields of an invoice record and can also lock all fields of all line items in the line items portal for any lineitem record linked to a locked invoice record. That would appear to do what you want here. We use this method here where I work to lock invoices and their related line items when a status field in invoices changes to "printed".

                  The one added issue, which Bumper mentioned, is that record level access control does not keep the user from adding another related lineitem record for a locked invoice. Changing the relationship so that the user has to click a button to add a line item is one option. You can also use a validation rule that checks the status of the related invoice record to deny all changes when that field is set to its "locked" value. This seals that one loophole and does so at the data level so it works from all layouts in your system. I made my layout a bit more user friendly by adding an OnObjectEnter trigger on the portal that checks the lock status and moves the user back out of the portal if it is locked. That prevents the user from attempting to create a record in the lineitems portal only to get an error message after they have created the record and try to commit it.

                  • 6. Re: invoice solution question

                    Will, the following is from a post that Steven Blackwell wrote over on fmforums.com regarding record locking. Steven literally wrote the book on FileMaker security and continues to moderate the security forum on fmforums. This technique locks the parent record and lineitems records. Using this method you can control who and when the record is locked and when and who can unlock it if necessary. You can set the lock based on the time from last modification or my usual is during the printing. Another nice feature of PhilModJunk's use of script triggers is that you can avoid the rather nasty dialog about not having sufficent privileges and put up a custom dialog of your own.

                    Steven's post:

                    In several different venues over the past several days a number of different developers have inquired about processes for locking records in a table against further changes once a set of business rules are satisfied. 

                    Unfortunately in some instances the recommendations given in answer to these inquiries can fail and leave records vulnerable to hacking. These include the idea that layout access manipulation can somehow protect the record against being altered.

                     In my view the best and most secure way to institute a record lock process is to use the Record Level Access privileges options found in the Edit Privilege Set options.

                     For any given table, create a field named “Lock” of the type number, auto-enter data of 1. Check the flag saying “Prohibit modification of value during data entry.”

                     Then in the Manage Accounts & Privileges-->Privilege Sets-->Edit Privilege Sets--> Records, select Custom Privileges. In the Edit column for the table you want, select limited. Pick the field “Lock” from the list of fields.

                     In the Field Access column, select the field “Lock” and mark it for No Access. This affords maximum protection against the field’s being modified in an unauthorized fashion and altering the state of the record.

                     When the circumstances occur that cause you to want to lock the record, use a script set to “Run script with full access privileges” to set the field “Lock” to 0. If “Lock”=1, the default entered value, the record can be edited. If “Lock”=0, the record cannot be edited.

                     Do not try to lock a layout. That is a chimera and the tail has a real sting.

                    post ended.


                    There is a method that allows one to lock specific fields and leave the others subject to change, but it requires a separate "shadow field" for every field you want to lock, so if you have more that a couple of fields to lock it quickly get cumbersome and really screws up you schema.


                    • 7. Re: invoice solution question

                      I also want to set some records to be locked some of the time. I followed everthing that was said in the previous post and so far i have had no problem.  I am just not great yet at writing scripts.  Could you give me some sense on how you would write a script that when the lock field is set to 1 it allows editing and when set to 0 it doesn't allow editing? 


                      Thanks for any advice you can give me.



                      • 8. Re: invoice solution question

                        The easiest way to handle this is through Security, set up another account that has privileges to change a record. You can script the changing of accounts, the really hard part is to remember going back to the limited account. Depending on the number and types of users I would usually give the editing to a manager, who would be the only one to have that account's password and leave staff with the non-editing privileges.