4 Replies Latest reply on Mar 17, 2009 12:32 PM by Jade

    Combine data from two fields into one - Beginner!



      Combine data from two fields into one - Beginner!


      Can I do that?  I want to take data from two different fields (as they are entered) and combine them into one field.  I do not want to use Merge Fields since I have to export the data and need it to be contained in a defined field.

        • 1. Re: Combine data from two fields into one - Beginner!

          Hi Wendy,


          If you want to concatenate two text fields together, one way is define a third field that is a calculation with text result.


          The calculation looks similar to:


          your_first_field & " " & your_second_field


          The "&" symbol combines the text strings.  In this case, the first field is combined with a space and then with the second field.  

          • 3. Re: Combine data from two fields into one - Beginner!



            I am trying to do that from a related table, I cannot get it to work except in the table that the data is in.  So for instance: I have a Tasks layout and I want to put the complete name of the person doing the task on the Tasks layout. In layout mode I try to enter the Personnel name, which is a calculated field from the Personnel table, in the Tasks layout, but it will not work...



            • 4. Re: Combine data from two fields into one - Beginner!

              storywizard wrote:


              I want to put the complete name of the person doing the task on the Tasks layout. 

              Hi SW,
              I'll start from the beginning making the assumption that there is only one person doing each Task to keep it simple.
              The trick here is to link the Person record (parent) to the Task record (child) using the Task layout. This will become a one-to-many relationship.  That is, one person can be assigned to many tasks but not the other way around.
              Now do you have a unique identification for each person on the Person table?  Something like an employee number, code, id, or auto-incrementing field will do.  It is important that this number be unique and unchangeable unlike employee names.  If not, add one.
              Next, you must also add this number as a field to the Task table to serve as a foreign key that links each task to its person.  Now you can use the Relationship Graph (File>Manage>Database…Relationships) to draw a connector between the Person::employee_id <= Task::employee_id.
              After you have created this relationship, you can use it to advantage on the Task layout.  Create a field using Task::employee_id defined as a drop-down list with the "Display data from:" set to Task::employee_id.  Set the "Display values from:" to Manage value list (found at the bottom of the drop-down menu).  This opens another dialog where you can define a new value list.  Click the New button.  Select "Use Values from field" and then click "Specify field".  Here you want to use the employee_id from the Person table.
              Bear with me, we're almost done…  On the left list, pick the Person table and choose employee_id.  On the right list, click the "Also display values from a second field" checkbox and then select employee_name.  At the bottom, choose "Include all values" (and optionally "Show values from only second field" ).  To finish things up, click all the OK buttons to close the open dialogs. 
               What all this has done (hopefully) is create a drop-down list on your Task layout that will display all the persons. When you choose one person from this list, the employee_id will be recorded in the Task record thereby linking the person to the task.
              When you reach this point, then you can put the complete name on the form by joining (& ) the related fields from the Person table as described above. 
              This is harder to describe than do so please let me know if you encounter any problems along the way.