I was almost sure that I could restrict users to modify a record flagged with "closed" using Access Privilege.

Is it possible with FM 5.5 or do I have to move up to 6.0.

I'm stuck here for two hours.

I can see the option "limited" in the "Modify" Menu in Access Privilege. It leads me in a calculation box...

I set it to If (flag = "closed",0,1)... or Flag # "Closed"...

It doesn't work. Please help.

You do not have to do that in security settings; it can be in field options, validate by calc...

HTH

Well, refearing to your latest post on the same topic, I'm quite sure you just "copy/paste" this answer from your clipboard. Or is there another function on this forum I am not aware of. I only knew about the "Add to favorite threads" button

Sorry I have been too vague here.

I surely wouldn't modify an invoice when it is closed. But I want to keep a specific field "closed" (not allow modification) within a specific record for some users vs "opened" for the Master.

Besides, using a validation by calc would not be useful as the field here is a lookup field, and I noticed that even with a "validate by calc", the "lookup" was superior than the "validate by calc".

I'm really lost here...

Are you suggesting I use a calc with Status(CurrentUser)...

What are the steps for a "Access Privilege" Record by record and field by field...

Validation can do all of this, but it depends on your design.

OK, I think I must see by myself in the book, have a test file and come back with really troubleshoots on the implementation. Thanks.

Just one question though :

How can I lock a field "price" in specific records in order that all other "price" fields be updated by lookup except thos locked (with all records opened !) ?

Am I wrong when I said that lookup is superior to validate by calc. I tried to set a field with validate by calc (strict + message), but the field would change according to lookup (ignoring the calc). Did I miss something ?

Invalidate the key field used for relationship for the lookup, like this:

Case(locked,ID & " 0", ID)

where ID is whatever fieled you have for the left key of relationship used in lookup.

Also set option for look up to don't copy if no match

Dj

Hi DJ, Thanks for the help.

Just to make sure I understood.

Relations : Line Item::Product by Product_ID

In Line Items :

Product_ID

Price (lookup from ::Product:Price)

Then you suggest Product_ID would be validate by calc

Case(Flag="Locked", Product_ID&0,Product_ID)

Hi DJ, Hi everybody,

Whatever I did, it doesn't work.

1. A simple Line Item file related to Product File using n_Product_ID as the key at left and right of relationship.

2. n_Price and t_Product Name in Line Item are set as lookup from t_ProductName and n_ProductPrice in the Product File using the relationship.

3. A number field for n_Invoice N

You do not want to relookup prices in Invoices. It will be mess. Price is historical value and as quoted or invoiced should stay the same.

Well, that's not exactly what I meant.

The formula I gave you is not for validating the field, but for preventing the look up to occur.

If the price is the only field you wish to "protect" from updating, than I suggest you to create an additional relationship, let's call it "conditionalLookUp" form Line items to Product file.

The left key for this relations should be the calculation I gave you:

recLocked=Case(locked, Product_ID & " 0", Product_ID)

or without extra field locked

Case(not is Empty(InvoiceN

Oh DJ, that is exactly what I needed !

I looked to all forums and never come accross with this "invalidate lookup" solution. Greaaat ! Yet another "genius" idea...

I will try this immediately.

I will probably come accross with another problem in my real implementation as my line item is "multi" (customer order and Invoice are in separate lines in the same line item, using a common ID), so the Case(flag, Product_ID & " 0", Product_ID) will becomes Case(selfjoin::flag, Product_ID & " 0", Product_ID)....Not indexable...

I will therefore have to use another sjlookup to index the "invalidation" of lookup !

Oops : Forgot to ask. Why did you say If the price is the only field you wish to "protect" from updating ? Why wouldn't the Product_name (also lookup) be "protected" as the "price" using this formula ?

The second solution given is actually the one I actually use (scripting a find to isolate the records for relookup), but I agree your solution is more secure.

I have to give you some point on my system (that will explain my needs for DJ's solution).

1. My Product file holds Name, Conditioning, Prices and Inventory.

2. My Line Item file is Multi-line (see sample section), that is that each line is dedicated to a unique module (quote, customer order, PO, shipping, invoice), using a "common" ID to group modules each other.

Name, Conditioning, prices and Qty in stock are brought through lookup to the line item by Product_ID.

3. When creating a new line in Line Items for a Customer Order, the products specs (name, conditioning, prices,...) are filled using the "common ID" to import the records from the corresponding Quote/Proposal.

4. But I also need to import the "Qty in stock" (that could have changed from the time the Proposal was made) through a lookup from Product_ID without altering the "Price".

Therefore, DJ's solution allows to use two different lookup (one classic and one conditional (if a Quote exists, lock "prices", if an Invoice exists, lock all fields)...

Thank you