Best Practice For Limiting Record Creation

[Roark Holz]

I have been working on a purchase order database and have run into a bit of a brick wall.

Basically I have a "Purchase Orders" table and a "Line Items" table. When a user creates a new purchase order I want to limit their ability to add entries to the "Line Items" table based on certain criteria.

There is a field in Purchase Orders called po_originator and another called po_status. I only want a user who is the originator of the PO to be able to add line items and only when the po_status is set to "Pending Approval"

In Define Accounts and Privileges I can use "Limited" for their ability to edit records in Line Items by doing the following...

Records can be edited when:

( PURCHASE_ORDERS::po_originator = Get ( AccountName ) ) and ( PURCHASE_ORDERS::po_status = "Pending Approval" )

but there is no option to set up limited privileges for creating records.



So I tried to do it by validating the fields that can be entered into Line Items, right now I have just this one validation on a field called li_invoice in Line Items...

Case (



( ( PURCHASE_ORDERS::po_originator = Get ( AccountName ) ) and

( PURCHASE_ORDERS::po_status = "Pending Approval" ) );

1;



0



)

but the validation always fails when entering the first line of data onto a new PO and for the life of me I can not figure out why (I will admit that I am fairly new to FileMaker so may be missing something very obvious). The weird thing as that the validation does seem to work on PO's where there is at least one line of data entered already. I have used the Data View to watch the value of my fields at the moment I create a new PO and it seems that the validation should pass.

Is there some better way to do what I am trying to accomplish?

Thanks in advance.

Roark Holz

[IdealData]

This sounds like the 1st line item is being created immediately after the PO header is created WITHOUT the PO header being COMMITTED.

Basically the PO header will only be committed if you have scripted it or the user clicks outside the fields on the layout.

Have you scripted your data entry or is it entirely upto user control?

[Roark Holz]

I thought that that might be the problem too so as a test I created a script that basically did a new record step and commit record step but when I entered into the Line Items table I still had the same problem.

Also, if I monitor the po_status and po_originator fields they set themselves immedately upon creating a new PO.

[IdealData]

Please post your file as I'm quite interested.

[Roark Holz]

I appreciate your help, as I said I am fairly new to Filemaker so it is quite possible that I am missing something obvious.

Here is a link to the file...

http://roark.oppco.org/PurchaseOrders.fp7.zip

I have made the admin password admin. The password for the different users is simply their first name in lower case but you can test the problem I am having now logged in as admin, just create a new PO and enter something in the "Inv. #" field of the line items. Eventually I will assign the validation calculation to all of the enterable line item fields but for testing purposes it is only set up on Inv. # right now.

The calculation can be viewed by looking at the LINE_ITEMS table and the field name is li_invoice_number.

Thanks again for your help, it is greatly appreciated.

Roark Holz

[IdealData]

Okay, I had a look.

The relationship definition is wrong. you do not need a dual key for the relationship - just the PO number will suffice. The "line_items_id" part of the relationship is messing things up because it does not get evaluated correctly until the first child record exists.

When the first child record does not exist then the "keys" in the child become different to the parent and consequently the reletionship fails and so does the validation.

Subsequent child records are created based upon the parent "keys", which of course are the same as the first child record (check your data), which is consequently wrong.

It's almost lke a recursive definition.

I'm attaching your file with the fixed relationship.

HTH

Purchase_Orders.fp7.zip

[Roark Holz]

Yes, that makes perfect sense now that I look at it. I'm not quite sure why I set up the relationship that way in the first place. You have really helped me and it is very much appreciated. I was spending all of my time trying to determine why the case statement I was using in the validation calculation was not working and it never even occurred to me to check the relationship.

Thank you so much, you have helped stave off the onset of my complete insanity for at least a little while longer.

Roark