Checkbox determine subsequent field values / multiple dropdown option

[richeamesjr]

I’ve been using FMP off and on for over 10 years and almost all self taught. Almost all my databases were rather simple and then I would modifying some previously created ones by trial and error. Now I'm attempting to create version 2.0 of a database that I have up and running and I’ve stumped myself on how to proceed.

 

I have a database for a before & aftercare program that collects standard info name/address/phone etc. In the past we manually entered all the costs per child. I’ve decided that I would like to be able to have some fields auto populate from other data entered.

 

For Example

 

Dropdown choice of program AfterCare, BeforeCare, Lounge

 

then

 

Checkbox selection that has Monday, Tuesday, Wednesday, Thursday and Friday

 

I would like to have a Cost field that determines payment option based on program choice and number of checkboxes

 

Before School Care

5 boxes $110.00 per pay period

4 boxes $88.00 per pay period

3 boxes $66.00 per pay period

2 boxes $44.00 per pay period

 

After School Care

5 boxes $348.00 per pay period

4 boxes $277.00 per pay period

3 boxes $211.00 per pay period

2 boxes $141.00 per pay period

 

LOUNGE

5 Days $416.00 per pay period

4 Days $333.00 per pay period

3 Days $250.00 per pay period

2 Days $166.00 per pay period

 

 

I’ve tried working with dynamic menus but I can’t seem to get the checkbox field to pick the 2,3,4,5 day cost

 

It could be a simple number field that fills in $$ but it also needs the option to override cost entered. If a family has 3 or more children we discount the costs.

 

Thank you for any help or direction you can give me

Rich Eames

[doughemi]

Create a Rates table. It should have fields for Program, NumCheckboxes, and Price. Each of the lines in your list should be one record.

 

Create a calculated field in your main table NumBoxes = ValueCount(YourCheckboxField)

 

Create a relationship between the two tables:

YourTable::Program = Rates::Program

YourTable::NumBoxes = Rates::NumCheckBoxes

 

Make your Price field in your table a number field and set it to auto enter Rates::Price. Make sure to uncheck the "Do not replace..." checkbox

Edited February 16, 2017 by doughemi

[richeamesjr]

Thank you for the response I will give this a try

 

Much Appreciated

Rich