How Do I Get Values Not To Show Up In A Drop Down As They Are Claimed?

[Ed Bradley]

From a table called"floor" I need to be able to have a drop down value list that shows the remaining spots available to claim.

The other table called "spots" has a fixed number of records that are numbered 1 through 36. As the spots are selected from "floor" I have two calculation fields, one changes a yes to no once the spot is claimed. The other replaces the spot number with a "".

I tried several relationship combinations with no success. If I use one of the fields for the drop down that are a calculation I get a warning that the function won't work because the field uses a related, or other non-indexed field. I tried several thing but I am stuck.

I am grateful for any help,

Thanks,

Ed

[comment]

This part is not clear:

As the spots are selected from "floor" I have two calculation fields, one changes a yes to no once the spot is claimed. The other replaces the spot number with a "".

How do you select multiple spots for a floor (i.e. where is the selection recorded? If a spot can belong to only one floor at a time, then the selection should be recorded in the Spots table, by entering the parent's FloorID into a "foreign key" field. If two floors can "claim" the same spot at the same time, then you are missing a third "join" table for recording the selections.

[Ed Bradley]

This part is not clear:

How do you select multiple spots for a floor (i.e. where is the selection recorded? If a spot can belong to only one floor at a time, then the selection should be recorded in the Spots table, by entering the parent's FloorID into a "foreign key" field. If two floors can "claim" the same spot at the same time, then you are missing a third "join" table for recording the selections.

Sorry I missed your reply, I did not have the email notification checked.

Each party selects a spot off of the floor reservation table(layout). When a spot is taken (reserved) by a client the spot table records the reservation by changing the yes in the available field to no and another field performs a calculation which replaces the spot number with a "". In the floor reservation table(layout) I need for the drop down value list to only show available spots.

Ed

[Ed Bradley]

This part is not clear:

How do you select multiple spots for a floor (i.e. where is the selection recorded? If a spot can belong to only one floor at a time, then the selection should be recorded in the Spots table, by entering the parent's FloorID into a "foreign key" field. If two floors can "claim" the same spot at the same time, then you are missing a third "join" table for recording the selections.

Sorry I missed your reply, I did not have the email notification checked.

Each party selects a spot off of the floor reservation table(layout). When a spot is taken (reserved) by a client the spot table records the reservation by changing the yes in the available field to no and another field performs a calculation which replaces the spot number with a "". In the floor reservation table(layout) I need for the drop down value list to only show available spots.

Ed

I tried to attach the file clone but here are tiffs, site does not allow the posting of fp7 files

[comment]

If I understand this correctly, each member record can have at most one spot - and each spot can belong to one member at most?

BTW, what does a record in the FloorReservations table represent? If it is a member, why isn't the table named Members?

site does not allow the posting of fp7 files

You need to zip them first.

[Ed Bradley]

If I understand this correctly, each member record can have at most one spot - and each spot can belong to one member at most?

BTW, what does a record in the FloorReservations table represent? If it is a member, why isn't the table named Members?

You need to zip them first.

They can have more than one spot, but hardly ever would it be more than 6.

A record would represent one party reserving spots.

Thanks for looking at this,

[comment]

I am afraid I still don't get it. There is a field named "Spot" in your FloorReservations table. It is a Number field. Value list aside, I cannot see how you can select more than one spot this way. I think you need three tables here, probably something like:

Members -< Reservations >- Spots

Once you have the correct structure in place, you can use this technique to control the value list:

http://fmforums.com/forum/index.php/topic/51074-tough-one-excluding-value-list/page__p__266667#entry266667

[Ed Bradley]

I am afraid I still don't get it. There is a field named "Spot" in your FloorReservations table. It is a Number field. Value list aside, I cannot see how you can select more than one spot this way. I think you need three tables here, probably something like:

Members -< Reservations >- Spots

Once you have the correct structure in place, you can use this technique to control the value list:

http://fmforums.com/forum/index.php/topic/51074-tough-one-excluding-value-list/page__p__266667#entry266667

Making more than one spot reservation would be accomplished by adding 7 more fields that each would trigger the Spot records to show that the spot was taken. But one or eight are dependent on the drop down value list only showing the available spot numbers.

I looked at the example. I could not get it work. I can get the spot number to appear in the drop down with the availability status but can not get the drop down list to show only the available spot numbers. If I try to use a calculation field as the drop down list an error comes up that it won't work. and if I try related record it results in the drop down list not operating at all.

Maybe I am not explaining this well enough. A dinner is being held to raise funds for this organization. Joe calls in to Sally. Sally is using this database I am trying to compile. She needs to enter Joe as the sponsor of a table of 6. Joe chooses a table near the exit and the podium. Those spots (31, 32, 33, 34, 35, 36) should now be claimed and no longer available.

What I have so far is that when the spots are claimed, the records in spots that represent each spot(1 spot = 1 record) show that it is not available. I need to have the drop down value list not show the spots 31 -36 since they are not available. In the sample file DwindlingVL which field should I use for the spot location field?

[comment]

Making more than one spot reservation would be accomplished by adding 7 more fields

That - to put it mildly - is not a good approach. Just as an example: to figure out which spots are taken, you would need need to pull an aggregated list from each one of the 8 fields and combine them all together.

Joe chooses a table near the exit and the podium. Those spots (31, 32, 33, 34, 35, 36) should now be claimed and no longer available.

More confusion here, I'm afraid: Did Joe reserve a TABLE - or did he reserve 6 SEATS? Will all people reserve entire tables, or is this optional (i.e. some reserve entire tables, some individual seats only)?

More points that require clarification:

Is this a permanent venue with 36 seats in total? Or will there be events taking place in venues with other number of seats?

How will you handle a situation where you need to register people for two events taking place within a short time span? It seems to me you also need a table for Events - in addition to Members, Seats, Reservations (and perhaps Tables?).

[Ed Bradley]

That - to put it mildly - is not a good approach. Just as an example: to figure out which spots are taken, you would need need to pull an aggregated list from each one of the 8 fields and combine them all together.

More confusion here, I'm afraid: Did Joe reserve a TABLE - or did he reserve 6 SEATS? Will all people reserve entire tables, or is this optional (i.e. some reserve entire tables, some individual seats only)?

More points that require clarification:

Is this a permanent venue with 36 seats in total? Or will there be events taking place in venues with other number of seats?

How will you handle a situation where you need to register people for two events taking place within a short time span? It seems to me you also need a table for Events - in addition to Members, Seats, Reservations (and perhaps Tables?).

an entry in one of the eight fields would trigger the spot to be claimed. If I could get the drop down list to show the unclaimed spots my issue would be solved.

Joe reserved spots which happened to make up a table. Spots are what is being tracked.

Other venues will have varying spots(seats). I already have the script to create the spots(seats) to each venue's needs.

Events are identified by an id created when an event is created. I have a table for events, attendees, Spots and reservations.

I hope this clears up any confusion. I do appreciated your looking at this.

[comment]

an entry in one of the eight fields would trigger the spot to be claimed. If I could get the drop down list to show the unclaimed spots my issue would be solved.

As you have discovered, a value list cannot be based on an unstored calculation. This cannot be circumvented and therefore your initial approach is not going work.

It is, however, possible to define a value list to show values from related records only. IOW, if you can calculate a list of all taken seats, you can use the result to define a relationship with the Seats table as:

Events::cTakenSeats ≠ Seats::SeatID

This relationship will show only seats that are still available for the event, and you can define a value list to show only these records.

As I mentioned earlier, defining such calculation with 8 fields is unduly complex. But this is not only reason to move the selections to a table of their own. You will also have problems with finding and reporting.

[Ed Bradley]

As you have discovered, a value list cannot be based on an unstored calculation. This cannot be circumvented and therefore your initial approach is not going work.

It is, however, possible to define a value list to show values from related records only. IOW, if you can calculate a list of all taken seats, you can use the result to define a relationship with the Seats table as:

Events::cTakenSeats ≠ Seats::SeatID

This relationship will show only seats that are still available for the event, and you can define a value list to show only these records.

As I mentioned earlier, defining such calculation with 8 fields is unduly complex. But this is not only reason to move the selections to a table of their own. You will also have problems with finding and reporting.

You have convinced me. I will work move selections as you said. Thanks so much for your patience and help.