3 Replies Latest reply on Mar 22, 2010 10:07 PM by aussie_ben

    Drop-down menu to populate a second field



      Drop-down menu to populate a second field


      I am a newb but am making progress towards a simple database for a not-for-profit gourp. My experience is basically NIL.



      Table A: Location (dropdown list from Table B), Location Rate (see question below), Attendee Name, Arrival Date, Departure Date, Total Days (Calculate: Departure Date - Arrival Date), Total Cost (Calculate: Location Rate x Total Days x 'Table C-DailyRate')

      Table B: Location, Location Rate

      Table C:DailyRate (global field)


      I  have sucessfully implemented the drop down value list in 'Table A - Location' from values 'Table B - Location' but want to get the Location Rate to come across also.


      How do I get the Location Rate to come across when I make or change a selection in the Location Drop Down menu, thus making the calculation of the Total Cost for an attendee?


      I appreciate any help in this regard. I'm using FM9 on Mac 10.5.8

      I can also post screen shots of relationship or field types etc if that helps!



        • 1. Re: Drop-down menu to populate a second field



          Does your relationship graph link Table_B -< Table_A (one-to-many) using Location as the key?  

          (If yes, I suggest that use a separate, auto-enter, serial number for the primary/foreign key instead because the Location text can change and may not be unique.)


          The Location_Rate on Table_A is probably for a specific attendee on a particular date.  Since location rates can change over time, I would use a Lookup which copies the current Location_Rate into Table_A from Table_B.   That way, the rate on Table_A remains the same even if it is changed on Table_B later.  


          Edit the Location_Rate field in Table_A and check "Looked-up value" in the Auto-Enter tab.  Specify Table_A as the "Starting with table" and Table_B as the "Look-up from related table".  Then choose "Location_Rate" as the "Copy value from field".  If there is no exact match, then select the "do not copy" radio button.


          P.S. Since the Daily_Rate is a global field (and apparently not related to a location), you can probably add this field to Table_B and drop Table_C.

          • 2. Re: Drop-down menu to populate a second field

            Location is where the attendee is from. Location Rate is dependent on Location and will represent a discount based on the distance travelled to the event, i.e. the further they travel the greater the discount - thus making the event a more equitable cost for all who attend.


            Location Rates may change over time.


            When using a "Look-up from related table" the value is not coming across to the field when the drop down menu item is selected. Thus my problem...


            Daily_Rate can be anywhere, I realise this, I just wanted it in a different Table. Cheers though!

            • 3. Re: Drop-down menu to populate a second field

              So I made it happen! Probably a work around and so am happy to hear others' solutions!


              I made TableA:LocationRates a look-up field from TableB:LocationRates with a "do not copy" match value

              with a Data entry of (number) 0


              I made relationships graph of:

                        TableA:Location              =     TableB:Location

              AND   TableA:Location Rate     X     TableB:LocationRate


              So on a new record in Table A, the LocationRate has a value of 0 before any data is input from the drop down menu of Location (based on a value list generated from TableB:Location).


              Once a value is selected in TableA:Location the TableA:LocationRate looks up the value to replace it as the "X" places the value in no matter what is, as the relationship MUST be the same value for both fields with the AND scenario of the first line.


              I hope this makes sense!