Unique Fields in Portal

[Kimmie]

Hi All,

I am having a hard time trying to explain this one! I have a database for a tradeshow. I want to keep the same database for each year of the show to hold history, money spent, points, etc. I have built a portal to capture this data. The problem I am having is there is a booth# field that is unique but if one of the years show has past and we go into the second year (same portal) all the booth numbers are taken because its looking at the year prior. Do i need a calculation to fix this or is the portal a bad idea to capture this?

[mr_vodka]

You can change your validation to a calculation; you should be able to concatenate the year field as part of it.

[Kimmie]

It doesn't seem to work either but in all honesty I am not sure if I did it right. Right now, I have one booth# field that holds a unique value. I tried to go in to validate by calculation but the few things I tried didn't work. I wrote Show = 2008. Any suggestions?

[comment]

See if this helps:

http://www.fmforums.com/forum/showpost.php?post/163868/

http://www.fmforums.com/forum/showpost.php?post/204677/

[Kimmie]

: didn't help. With the first one it is calcualting two different fields and then making that unique. I am trying to say that if the show year is 2008, than the number in this field is unique. BUT I would need to say it without specifying an actual year because it will be for all years. Thank you in advance for your help with this!

[comment]

I think both John and I assumed there would be a field for booth number, and a field for the year. The combination of booth and year would be unique. It seems you want something else, but it's not clear to me what that is. More information would be helpful.

[Kimmie]

I do have a separate field for both the show year and the booth# and they are in a portal. I think I think I am the one misunderstanding (obviously!). The booth number is unique. When I try to add a calculation for the verification of the booth number field, I wrote Booth# "&" Show Year. I tried a few variations but it didn't work. I assume that it would make it unique? But I am not finding the correct calculation. Please forgive me, the answer is probably simple but I am completly self taught and try to logic my way through everything. Thanks~

[comment]

I am completly self taught and try to logic my way through everything.

Me too. :

I am still not sure I understand the situation in general. It would help to know what your solution is tracking (are you the tradeshow, or are you an exhibitor?), what tables you have and what are the relationships between them.

In case the original guess was correct: to make sure that the combination of booth and year is unique, you must create a THIRD field of type Text, and have it auto-enter a calculation (with replacing existing value) =

[color:blue]Booth# & "|" & Show Year

Then, under the Validation tab, require the field to have unique value.

To clarify: we are NOT talking about validation by calculation. The calculation is defined under the Auto-Enter tab.

[Kimmie]

Hi!

Yes, I work for a tradeshow company so i track all exhibitors, sponsors, etc. Each year my database holds all the company info, financials, and booth spaces (size & number). The reason I have the unique number for the booth is to stop double booking of that particular space. Previously I had a new database each show but this runs into problems because i need to track history, the exhibitors accumulate points each year, they change company names like wild so I wanted to make one DB to carry all. I always wanted to build a floor plan in FM so that i can track on one page what booths are open, etc. but I could NEVER figure that one out. No suprise since I can't figure out this one out either (hahah) :

I did what you said and yes it works! Thank you! I have to export this data weekly to send out to sales staff and may play with this to find a way to not use the year. Maybe I'll even toy with the idea of building a floor plan to highlight booth numbers as they get used. Any thoughts on whether you think FM can do this?

[comment]

It's possible, if you keep it simple. Still a lot of work, though. Search the forums for +floor +plan to get some starting points.