Jump to content
Sign in to follow this  
angelleye

How can I add validation based on related data..??

Recommended Posts

Let me start by saying this is kind of a mess, but I have to work with what I've got at the moment, so bare with me please.

We've got tables: Invoices, InvoiceItems, PurchaseOrders, PurchaseOrderItems. These are just a few, of course.

InvoiceItems is related to PurchaseOrderItems on a field called ControlNumber. This field is what brings in all the related cost information for reconciling the invoice.

There is another calc. field in both InvoiceItems and PurchaseOrderItems called POItemCalc that has been created (I did not make this part which is partly why I'm a little lost).

This could get long and drawn out but that field is created by combining the PO and the Item number for that particular item.

So that relates InvoieItems to PurchaseOrderItems in a way that allows us to tie a drop down box of all available control numbers for that item to select in InvoiceItems. However, it's also been requested that you still be allowed to type directly into this field as well.

With that, we're having issues where control numbers are getting added in InvoiceItems that are invalid. Just typos or whatever, but I need some validation that says if it's not in the drop down box then you can't use it.

I don't know if that's way too much information or what, but anything you can tell me on how to get that validation to work would be greatly appreciated. Thanks!

Share this post


Link to post
Share on other sites

I don't know if I'm on the right track or not but I created a global field in my PurchaseOrderItems table and put a 1 in it. I then went back to InvoiceItems and added the following calc. to the validation of the control number field:

Sum ( PurchaseOrderItems::gControlNumberValid ) > 0

After that no matter what I put in the control number field I get the message that says "only certain values are allowed" and it lets me revert or accept the invalid value.

So first, I don't unerstand why it fails the validation when the relationship does exist (the control number shows up in the drop down) and then I'll also need to figure out how to make it not allow the invalid value and force you to fix or revert.

Share this post


Link to post
Share on other sites

Now that I look a little closer...it seems that whatever my validation calc. ends up being needs to equal what is typed into that field in order to pass, is that correct?

If so, then how do I make it look at ALL related reocrds to see of ANY of them match what was entered?

Or if that's not correct, where am I getting lost here?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.