5 Replies Latest reply on May 28, 2009 12:47 PM by philmodjunk

    Question to Table and Field Relation in a Layout

    DanielLang

      Title

      Question to Table and Field Relation in a Layout

      Post

       

      Dear Forum Members

       

      I have a (I hope so beginner) question related to a layout I want to realize.

       

      I have 2 tables:

       

      table 1 called customers with the following fields

       

      cutomerid

      name

      firstname

      address

      state

      country

      phone

       

      table 2 called states with the following fields

       

      stateid

      statename

       

      The relation between these two tables is set trough customers::state = states::stateid

       

      Now I have created a layout for the customers table. This was so far no problem. But now the problem comes...

       

      The records from the customer table has in the field customers::state the values of the field states::stateid stored.

       

      But I want to have in the layout a selectionlist for that field where all related entries from states::statename are shown. The should be able to pick one statename from that selectionlist and the user should not see the valuse from states::stateid.

       

      Now when the record is saved i want to have stored in my customers::state field the related value from states::stateid and not the visible states::statename.

       

      Also when the user scrolls trough existing records, he should only see the value from states::statename (which have to be translated automatically from states::stateid to states::statename.

       

      Here I will also give an example:

       

      In the field states::stateid the records have the following content (for example):

       

      NY

      CA

       

      In the table states the are following values:

       

      states::stateid, states::statename

      NY , New York

      CA , California

       

       

      Now when user enters a new customer record I want, that the user can see for the field state only:

       

      New York

      California 

       

      and not the codes from states::stateid. But inside of the customer record, the relate value from states::stateid stored.

       

      I hope this explanation was not too confusing... I was trying all kind of different things, but I never reached the result I wished to have.

       

      Has someone an idea how to define that ?

       

      Regards, Daniel