5 Replies Latest reply on Nov 16, 2013 7:57 AM by philmodjunk

    change field to lookup

    john9210

      Title

      change field to lookup

      Post

           FM 12. I have a field that reads data from another table. I would like to change it to a lookup. Will I loose all the data that was previously entered?

        • 1. Re: change field to lookup
          philmodjunk

               I think you need to carefully describe what you mean. In my book, a field that looks up data from another table IS a field that reads data from another table.

               Is the field currently a data field with an auto-enter field option that "reads" the data? Or is it a field of type calculation? if it's a field of type calculation, it isn't storing any data in the field now.

          • 2. Re: change field to lookup
            john9210

                 I have two tables: Visits (parent) and Specialists (child).

                 In the parent table I’m using a value list on the foreign key field fkSpecialistID that allows me to insert the primary key pkSpecialistID from the related record in the child table.

                 Currently, the foreign key field is a numberfield with no aouto enter options. At this point, there are about 200 records in the child table.

                 I would like to change the foreign key field to a lookup so I can delete child records without losing data in the parent table.

                 Since no data is currently being saved, is there a way to import the data after I switch the field to a lookup?

            • 3. Re: change field to lookup
              philmodjunk

                   Actually, that would be seen as Specialists(parent), visits (child) as it appears that you have this relationship:

                   Specialists-----<visits

                   Specialists::pkSpecialistID = Visits::fkspecialistID

                   

                        I would like to change the foreign key field to a lookup so I can delete child records without losing data in the parent table.

                   You don't need to do that and can't do that anyway. Deleting a record in one table does not delete records in other tables unless you have selected the "delete" option in a relationship linking the two tables. Just clear that option and the related records are no longer automatically deleted.

                   

                        Since no data is currently being saved, is there a way to import the data after I switch the field to a lookup?

                   That's not what I posted. I said IF the field is of type CALCULATION, there is no data stored in this field as it will be an unstored calculation field. Since this is actually a number field, that comment does not apply to your situation.

                   Is it possible, that by "look up" you want to be able to click on the fkSpecialistID field and select a specialist from a value list? That could easily be done if that's what you need here.

              • 4. Re: change field to lookup
                john9210

                     Phil:

                     You don't need to do that and can't do that anyway. Deleting a record in one table does not delete records in other tables unless you have selected the "delete" option in a relationship linking the two tables. Just clear that option and the related records are no longer automatically deleted.

                     My relationship does not allow deletion. When I delete a child record I lose the data in the parent record. I thought a lookup field in the parent table would retain the data.

                     Is it possible, that by "look up" you want to be able to click on the fkSpecialistID field and select a specialist from a value list? That could easily be done if that's what you need here.

                      

                     Let me try to explain more clearly what I’m doing. see the uploaded image.

                     A value list (named Specialists) attached to the pkSpecialistID field is used to display the specialist name in the field. The value list is set to display values from two fields: first field: pkSpecialistID; second field: SpecialistName. Both are in the same TO (Visit_SPEC).

                     So when the user clicks on the fkSpecialistID field, the value list displays a list specialist names. After selecting a name, the field displays the name only, not the ID.

                     If I delete a specialist record, the ID value continues to appear in the field but specialist’s name does not. Why should the ID continue to appear after the child is deleted? I guess, the field is storing the ID but not the name?

                     So I really want to store the specialist name in the parent table so I can delete the specialist record and now lose the name.

                • 5. Re: change field to lookup
                  philmodjunk

                       Why should you delete the record at all?

                       You don't and can't change this field to look up data. But you can define another field in your table to look up the name. You are correct that the popup menu value list format on a field will enter the ID number, but show the name if your value list is set up to do that in the first place. When you delete the related record, there no longer is a related record to supply the name and the field reverts to displaying the data in the field.

                       If you want, you can change your field format from pop up menu to drop down list, then place the added lookup field on top of the drop down list field so that it is hidden behind it. Use behavior settings in the Inspector to deny Browse mode access to the look up field. In Browse mode, when you click on the name field, the hidden drop down list pops to the front and deploys. Once you select a value in the list, it disappears back behind the look up field that now displays the looked up name that you selected in the drop down list.