6 Replies Latest reply on Mar 29, 2013 11:05 AM by philmodjunk

    Make a field a simple list like Bento

      Title

      Make a field a simple list like Bento

      Post

           I am making a database that tracks which doctors have referred what patients so I am wanting to add a "simple list" in the Doctors record. 


           I am coming from Bento and I was able to make a field a "simple list" similar to an excel spreadsheet with rows and columns. Is there anyway to do that inside of a field with FileMaker Pro 12?

        • 1. Re: Make a field a simple list like Bento
          philmodjunk

               While you could list multiple entries separated by returns in a single text file, it's really not the way to do this in FileMaker.

               Make a related table for your referrals and set up a portal to that related table for recording them, one referral to each related record.

          • 2. Re: Make a field a simple list like Bento

                 That wont quite work because I have about 4-5 doctors which I have indicated will each be new records. Everytime I go to 'Dr. A's record I want to be able to see a list of all of the patients he has referred. 

            • 3. Re: Make a field a simple list like Bento
              sparrow_design

                   Hi Esmeralda,

                   Phil's technique WILL work...

                   1. set up the relationship From Dr's Table TO Referrals Table (one to many)...

                   2. each time a Client is referred (new Referral Record), that Dr's ID needs to be entered into this new Referral record (enter into the foreign kjey field in this table)...

                   3. Then use that field to link from Dr's primary ID field in relationship in #1.

                   A portal in Dr's Table using that relationship WILL NOW show all referred patiens.

                   Boom!

                   Alternately...if you really PREFER to have the related data in ONE FIELD...use an unstored calcualted field in Dr's Table (relies on same relationship as above) along the lines of this:

                   List ( ReferralTable::Patient_Name ). This will populate the field with list of Names...if you need more data than that (Like Names & Phone #'s)...use a stored auto-enter calc field in Referral table that concatenates the 2 or 3 fields need for display...and you can enter spaces and 'column' sperators in this auto-enter field as well.

                   Cheers,

                   -David

              • 4. Re: Make a field a simple list like Bento
                philmodjunk

                     To expand on that a bit more, you might set it up like this:

                     Clients----<Referrals>-----Doctors

                     Clients::__pkClientID = Referrals::_fkClientID
                     Doctors::__pkDoctorID = Referrals::_fkDoctorID

                     For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                     You can then put a portal to Referrals on your Clients layout and setup Referrals::_fkDoctorID as a drop down list for selecting a Doctor that you have recorded in the Doctors table. You can also include fields from Doctors in the portal row to show additiona information about the selected Doctor such as their name, specialty, contact info...

                • 5. Re: Make a field a simple list like Bento

                       I think I may be misunderstanding or not explaining myself correctly. I have made 3 tables: Print Referrals, Online Referrals, Doctor Referrals. Those are the three categories/tables I am working with. Each record within the table will be the location of those ads for example:

                       Print Ad:

                         
                  •           Magazine A
                  •      
                  •           Magazine B
                  •      
                  •           Flyer  A
                  •      
                  •           Flyer B

                        

                       Online Ad  

                         
                  •           Website A 
                  •      
                  •           Website B 
                  •      
                  •           Website C

                        

                       Doctor Referral

                         
                  •           Doctor A 
                  •      
                  •           Doctor B 
                  •      
                  •           Doctor C

                        

                        

                       Whenever I have a patient that says they were reffered by a doctor/print ad/online ad, I will go to that record and add their information to a field that I am hoping will be a simple field so that I can add the patients name address and phone number and also be able to tally the amount of patients reffered per referral. 

                       I am using this as a way to track my marketing campaigns to see what works and what doesn't. 

                  • 6. Re: Make a field a simple list like Bento
                    philmodjunk

                         Do you also have a table for Patients? You need a table where you have a record for each patient. Then you link that record as needed to records in your other tables.

                         Over all, I think you need to rethink this design and use a different data model.One thing that you don't want to do is enter the same information on a given doctor referral or Ad over and over again. Instead, you'd link all patients mentioning that ad/referral to the same record that documents that record or advertisement.

                         Patients------<referrals>-----Doctors (you've seen this already)

                         plus:

                         Patients-----<Referrals>----Advertisements (I'd use the same table for web and print advertisments.)

                         Instead of "going to their record", you create a new Patient reocrd, enter patient name and other info on that patient and then link it to any relevant referring Doctors and/or Advertisments by selecting them from a drop down list or other more sophsiticated value selection tool.

                         And you can use reports based on The Referrals table to get a count of how many patients listed each advertisement and referring doctor.