Unique calculation via a portal?

[Steve G]

My database has a table called SHOWS with a layout of the same name, and a table called LINKS EXHIBITORS TO SHOWS ("LETS" for short) with a layout of the same name. SHOWS has a portal that points to LETS.

One of the fields in LETS is "Booth Number" and I'd like this to be a unique value *per*show*. I tried a couple of ways of doing this and ran into dead ends, so I need help.

I first tried creating a calculation field ("calcShowBooth") in LETS that puts the show ID, a space, and the booth number into a string. This worked but there's no way to have a calculation designated as needing to be unique.

I next turned that calculation field into a text field, auto-entering the same calculated value, and validating as unique. This worked EXACTLY as I wanted but only while I was in the LETS layout. When I tried it in my portal on the SHOWS table I got an error: "calcShowBooth is defined to require a value, but it is not available on this layout. Use another layout to assign a value to this field." So that doesn't work either.

How can I get this working via my portal?

[comment]

It's just the standard "validation failed" message - only worded differently.

To get back the "regular" message, you could put the field on the layout (it can be anywhere on the layout, not necessarily in the portal, and it doesn't have to be enterable).

Or use a custom validation message.

I'd also suggest you look into the "dwindling value list" technique. Validation is a good thing to have, but it's not the best user experience.

[Steve G]

I think you're reading too much into this. It's not the verbiage of the error message that I'm concerned about. It's the fact that I'm getting the error message, period.

I need a way to allow the user to enter a booth number into the LETS table via the portal on the SHOWS table. Adding a field to the SHOWS table would do nothing since the duplication effort that I'm trying to eliminate is not on the SHOWS table.

[comment]

I don't quite follow. The message you see is not an error message, it's a validation failure message. You are getting it because you have violated the validation rules you have set up. You're not getting this message when you try to enter a unique combination, are you?

Adding a field to the SHOWS table would do nothing since the duplication effort that I'm trying to eliminate is not on the SHOWS table.

I haven't suggested adding a field to the SHOWS table. I said you could put the field "calcShowBooth" from the LETS table onto the layout where the portal is. That would change the wording of the message - but it won't eliminate getting a message. That's (mainly) what validation does - it pops up a message when the users enters something that conflicts with the validation rules.

[Steve G]

Seems I once again did not use exactly the correct words that you require. Sorry, I'll ask someone else for assistance.

[Steve G]

I posted my original question on another forum and got this response, in case anyone is having the same issue:

The answer is very simple: Just put a calcShowBooth field in the portal. You can make it really tiny so that it's all but invisible. You can kind of think of each line of your portal as each record in your LETS table. If the line in the portal doesn't include the field needed for the calculation, then the calculation won't work and you'll get that error.

I followed this very straightforward response and it works perfectly. I actually hid the calcShowBooth field behind a small graphic I have in the portal (for deleting the portal record) so it is literally invisible.

So the answer is NOT, as comment says, to put calcShowBooth into the *layout* where the portal is, but to put calcShowBooth into the *portal* itself. That's an important distinction.

[comment]

I am still not sure what the actual issue here is. Perhaps you could post a simple demo file. Or, to make it easier, just modify the attached file in such way that moving the ValidateUnique field out of the portal and placing it anywhere else on the layout would make a difference.

ValidateUniqueJoin.fp7.zip

[LaRetta]

The thing is ... the validation failure must be corrected. And since the standard field exists in another table, it can only be fixed if it is on the layout. And ... if the record throwing the validation error isn't the first related record, it can't be accessed simply by being on the layout; it must be accessed through the portal. If user isn't allowed to override the validation, the User will be stuck. So the field(s) (and not the calculation) must be in the portal so it/they can be corrected.

Your advice from another forum was wrong ... the calculation can't be fixed. The validation failure can't be addressed. Just putting the calc in the portal solves nothing, as Comment was trying to explain.

Edited October 26, 2008 by Guest
Added last sentence