In the OrderEntry table I have portals to two other tables. When I enter a product number it fills in the product information from these two tables. I would like to be able to "lock" the product number once I have clicked or tabbed out of the field so that the only way to change the entry is to delete the portal row (and thus the related records in a line items type table). This is due to using this field in a look-up in another table which, of course, won't change due to being looked up so I need it to be deleted if the product info changes. The looked up value there is required for a calculation and needs to stay dynamic. I hope this is clear. I tried unchecking the allow changes button but it still allowed changes and my calulation stopped working.....!

Thank you for your time and thoughts. This is truly a wonderful forum.

Deborah and

My first thought is why do you need 2 tables for order entry?

You could try validation based on if the field is empty. The validation would have to be strict to enforce the "lock".

I wondered too, but you could also attach a script to [Product#] that evaluates if IsEmpty[Product#]. If not, script uses GoToField [Product#] and exits to allow user input. If a value already exists, show message; "Line Item will be deleted if you wish to change Product#"; Options (Cancel; Change), If(LastMessageChoice) = 2; DeletePortalRow(s), then GoToField [Product#].

Ralph:

The two tables thing has to do with the usage of the product items. Sometimes two of the product items are used together to create an order and sometimes only one is. I need to be able to show where & how to use the product (it is actually a fabric and the order item is a shirt) and keep track of the usage. The calculations have to do with combining the usage quantity based on the fabric (product) ID. What I need to be able to do is 1) enter all the fabrics ordered for a season ("products"), 2) order shirts made with these fabrics ("orders") & 3) keep track of how much fabric is used.

Since fabric used for the body of the shirts is used at a higher rate than fabric used for trim (like a collar) and often the fabrics are used for both, I need to be able to both have the information of how the fabric is used (so I can create order sheets to send to the people actually making the shirts) and also add together the fabric used whether for the main part of the shirt or for some smaller part.

I will try the validation idea. Can I make it strict when the field is alpha-numeric?

NFD:

I am not sure how to attach a script to the product#. I'm really new to this, so forgive me if this is something really obvious. Maybe I'm just confused with the terminology. I'd like to try this idea, too, as the warning message sounds like a good approach.

Thanks to you both!

Deborah

NFD,

I think I know how to do this after all. I will try it Monday. Part of my frustration is that the database is at work and I'm writing this at home. I don't really have steady internet access at work and I don't have FileMaker at home so I can't try things out until I get back to work and I mostly have to remember my questions (and all the details about my project) for when I'm home. Oy....

Thanks a bunch.

Deborah

You can show a message in RalphL's solution as well, by selecting Show Message if field validation fails. RalphL's solution applies a validation test to the field no matter where the field occurs in the database (better way to go)

But FM allows the <Prohibit modification of value during data entry> option, only when fields use the auto-enter function (it seems unlikely to me that [Product#] uses auto-entered values). I haven't figured out a validation calculation that works to check the current value of a field against its previous value using field validation alone. RalphL may have the answer to that?

To attach a script to [Product#], go to the field occurance on the Data Entry layout in LayoutMode, then Select <Button> from <Format> menu. Lastly, select <PerformScript> and designate the script. This will apply the script to ONLY that occurance of the field. For this reason RalphL's solution is more secure.

But your Database may only permit data entry in that one occurance, so this may not be an issue (you could, of course attach the script to other occurances as well, or make sure users may NOT enter other occurances of [Product#]).

Details on the validation method of locking fields.

In the related table create a new field, I will call it Lock. Make this field the same data type as the field to be locked. Under options slecte Auto -enter by calculation. The calculation is Product_Number. Make sure the calculation is set to not evaluate if all fields are empty and it is set to Not replace existing value if any.

Now set the validation for Product_Number to Validate by calculation and use Product_Number = Lock as the calculation.

After everything is working make the validation strick and add a custom message saaying that the product number can not be changed etc.

Hi RalphL,

Thanks much for Lock detail.

Thank you both very much. I am going to try these ideas as soon as I can (it IS Monday....).

Deborah