12 Replies Latest reply on Sep 1, 2011 1:26 PM by LewisRobinson

    Making a text field (edit field) into a drop down list

    LewisRobinson

      Title

      Making a text field (edit field) into a drop down list

      Post

      I have a series of links on each record from another program (HyperCard) that I'm trying to convert over to Filemaker Pro Advanced.  Each of some 40,755 cards (what HyperCard calls records) has a variable number of these links (Hypercard calls them buttons).  I've solved the problem of getting all the information about the links out of each HyperCard record (card) into a text field in an individual record in a filemakder table.  Is there a way to make this field into a drop down list globally, without individually going to each of the 40,755 records and doing it manually.  That way each link could be selected and I could use it to navigate among the records. 

      Thanks for your help 

      Luysii

        • 1. Re: Making a text field (edit field) into a drop down list
          philmodjunk

          Wow! That's a blast from the past. When I first opened FileMaker Pro 2.5 on my Mac LC, my first thought was: This is a lot like Hypercard...Wink

          Can you describe the structure of your table(s) in a bit more detail?

          I'm particularly interested in Those "variable number of hyplerlinks". Are these just links to other cards/records or links to webistes, files and such.

          Do you have one link to a field?

          SInce the number of links varies, do you have a group of fields for links in each record or did you split the record into pieces with one record for each link?

          • 2. Re: Making a text field (edit field) into a drop down list
            LewisRobinson

            Sorry to be so longwinded, but this explains what I'm trying to convert (and also why it's structured the way it is)

            The frequency of posts is about to diminish.  I've got to learn a new program, and its programming (scripting) language to shift my current database to it.  I started using HyperCard when it came out in the fall of 1987, using it for all the notes I took on my reading in various areas.  You can even draw chemical structures with it, but not easily.   It's a marvelous program and I'd be using it still if Apple still supported it (something they've not done in a decade).  When I was in practice, I ran its business side using it.  It's extremely easy to program, user friendly , enough so that I wrote my own billing software, saving a bundle.   When Apple moved to Intel processors, Hypercard didn't move with it, and so I had to buy 2 old machines using the PowerPC and OS 9.  The first versions of OS X had a classic mode which still supported Hypercard on the PowerPC.  The newer versions of OS X will not.  Machines eventually fail and I'm not about to have all the work I've put in, fail with it.

            The database  contains 14,093 cards in the Xref stack, which is basically text, 18,892 cards in the index stack, and 7658 in the Glossary stack.  The great thing about Hypercard is that it lets you link two cards in any stack together by buttons.  Why is this great?  Because science progresses by the unexpected.  No one would have thought that the gene for narcolepsy would be related to appetite, but it is.  All it takes is a button to link the two cards with information about either together.  So the index stack has 47,498 buttons, xref 34,282 and glossary 11572, bringing the total number of cards and buttons to a glorious 133,995.  The whole shebang takes up 72 megaBytes on Disk.

            This sort of thing was foreseen years ago in a great book called "Silicon Snake Oil: Second Thoughts on the Information Highway" by Clifford Stoll.  His point, is that storing things digitally is a con, as programs and their formats are no longer supported.  It certainly happened to me, although it's been a great 24 years.  He also wrote another very good book called the "Cuckoo's Egg" about how, as a graduate student in Astrophysics, he came across a few accounting errors in the accounts he was called to manages -- pennies only, as I remember. It led to the discovery of a ring stealing all sorts of money from accounts all over the world.  It's well written and funny.

            So I've decided to learn FileMaker Pro, which also has a scripting language and which appears powerful enough to do so.  I went to a training session today which introduced the basics, and was very pleased to see that the Broad Institute (of human genome project fame) and Tufts NeuroScience sent 3 people.  The program has been around a long time (in computer time, that is) and hopefully will continue.  I've found "FileMaker Pro 11, The Missing Manual"quite helpful, although I've only worked through 70 or so of its 800+ pages.

            I've already figured out how to get the text content of the various fields stacks and the scripts of the buttons of my HyperCard stacks into a plain text file which I can simply schlep over to the newer Apples.    Now I have to learn the scripting language of FileMaker Pro to get this data into a Filemaker database (which won't be hard to construct, since it will mimic the functionality of the old one).  If anyone out there knows of  a (free) good source explaining the FileMaker Pro scripting language, rather than just listing the commands and syntax, please  let me know.  While you can learn a new language from a dictionary, there are better ways to do it.  Today's training session already set me back $300 (well worth it though !), and FileMaker Pro Advanced will be $500.

            • 3. Re: Making a text field (edit field) into a drop down list
              philmodjunk

              After reading all of that, I have to say that it didn't answer my quesitons except to note that your hyperlinks are all internal links to other records.

              What is the structure of that text file?

              Do you have one row per card or multiple rows per card where there is one link to another card in each row?

              What do the links look like in this text file?

              In fileMaker you can certainly build relationships between records in the same table as many times as you need, but we need to know what raw material you have here to start with.

              • 4. Re: Making a text field (edit field) into a drop down list
                LewisRobinson

                Again, this is likely to be longwinded. Apologies in advance.   The HyperCard database has 4 stacks (each of which will be turned into a table I suppose).  Here is the structure of each

                Stack #1  XrefText

                  1. Title -- often a journal reference -- An example is "FileMaker Pro -- Notes"

                  2. Text -- my notes on FileMaker Pro (currently 22,521 characters  in 3,775 words)

                 3.  List of topics this particular record has been assigned to

                 4.  A bunch of buttons, linking it to records in this and other stacks -- this particular example has 5.  Their titles are

                  a. Portals -- links to another record in XrefText about portals

                  b. Unanswered Questions -- links to another record in XrefText about things I don't understand

                  c. Unicode -- links to a record in a different stack (table) called Glossary -- contains details about Unicode

                  d. HowTo -- links to a record in XrefText, which contains my notes on how to do things I might find useful

                  e. OtherTopics -- links ro a record in a different stack (table) called Index

                           The number of buttons on each record varies

                Stack #2 Index 

                  l. Title

                 2. Single topic this record has been assigned to

                 3. A bunch of buttons linking this particular record to records in this and other stacks

                An example would be the Index record with title Fms

                 it is assigned to the topic oncogenes (genes which cause cancer)

                 Fms has 3 buttons

                    BasicInfo -- links it to a record in the Glossary stack (table) containing a brief amount of information about Fms

                    MoreInfo -- links this record to a record in the XrefText Stack

                    All the Oncogenes -- links this record to another record in Index, containing a list of known oncogenes (which has 68 buttons) each linked to a particular  record either in Index or XrefText 

                Stack #3 Glossary

                Stack #4  Topics 

                The last two are similar -- the point is that any link (button) can go to another record in the same stack (table) or any of the other 3.  However, each button goes to only one record in one stack (table)

                Hopefully this explains the structure of what I've got to convert to FileMaker Pro Advanced

                Thanks for your help

                • 5. Re: Making a text field (edit field) into a drop down list
                  philmodjunk

                  Doesn't really help.

                  When you import this data into FileMaker, you've indicated that the data will be in a text file. We need to see that text file's format, not the structure of the original hypercard stack--though that information is useful in understanding what is listed in that text file.

                  You are able to export the hypercard data into a simple file format right such as tab or comma separated values right? (Otherwise we won't be able to do much at all with your hypercard stack data.)

                  Taking just a few parts of your example of your "bunch of buttons":

                  THis might be listed in a text file as:

                  TItle | Notes | "Portals" link | Unanswered questions link | Unicode link | How To link....

                  or it might be listed in this text file as:

                  TItle | Notes | "Portals" link
                  TItle | Notes | Unanswered questions link
                  TItle | Notes | Unicode link
                  TItle | Notes | How To link....

                  Where | could be a tab, a comma or....

                  And this is just two of many possible variations. We need to see this format as it dictates how to set up first a data import and then any "after import" scripts needed to establish proper links between records using FileMaker serial number fields as primary keys.

                  • 6. Re: Making a text field (edit field) into a drop down list
                    LewisRobinson

                    PhilModJunk -- Once again, thanks for taking the time and trouble to respond.  I've already been able to set up a table in FMP with the following fields

                     XrefTitle

                     XrefText

                     XrefTopics

                     XrefButtons HCXrefID -- each card in Hypercard gets a unique number (that's what card id is below)

                    FMXRefID -- the unique identifier given to records by FileMaker

                    + many more for screwing around with as I write scripts

                    I've written scripts which successfully get the data in a hypercard card (record in FMP) into the appropriate field, and can do this for 50 sequential cards putting them into 50 sequential records at a time (I haven't tested higher numbers, but I see no reason why this shouldn't work)

                    Here's what XrefButtons looks like for one particular card

                    2

                    card button "Flushing -- differential diagnosis"  go to card id 1409755 of stack "Index"

                    card button "Myopathies with other lipid lowering agents"  go to card id 42854

                    The problem is the best way to display this information and use it to navigate.  Drop down list?  Portal?  etc. etc.

                    Here's what's in XrefText

                          3 cases of myopathy associated with nicotinic acid (niacin).  The flushing seen 1 - 2 hours after ingestion is mediated by prostaglandins, and can be retarded by aspirin prior to nicotinic acid use. It is recommended that hepatic enzyme tests be monitored regularly in all patients on sustained nicotinic acid, rare cases of hepatitis and jaundice have been reported.

                          The myopathy reported in these 3 cases was quite painful.  Thus patients with polymyositis should be asked if they are on nicotinic acid.

                    Hopefully this is the information you need.  Happy to provide more
                    • 7. Re: Making a text field (edit field) into a drop down list
                      philmodjunk

                      That's what I needed. You have a potentially "many to many" relationship and I needed to see how the data for your record to record links were imported so that I could then suggest what to do from there to build the needed links for you.

                      I'm going to suggest first a "quick fix" that can get you up and working with the data as it currently exists and then recommend a structural change you can work towards that will be much easier to work with in fileMaker in the future.

                      We need a calculation to extract the Card ID's from the mass of text included with each. You appear to have a list of values separated by returns. In your example, is "2" the first value? What does it represent?

                      Your list of text entries are further complicated in that they link to two different "stacks" which should be two different tables in your database. I'm also guessing here that the end of each line of text is either the card ID for a record in the current table or 'of stack "index"'. We'll need to be more sophisticated with our approach here if this is not the case.

                      Quick and dirty fix:

                      Open Manage | Database | Relationships, click on the existing occurrence ("box") of your table and use the button with two green plus signs to duplicate it. You can double click it to open a dialog where you can give it a more descriptinve name.

                      Drag from FMXRefID to FMXRefID  to link them by this field. This is called a "self join" as you have linked the table to itself. This can give you a relationship like this:

                      YourTable::FMXRefID = YourTable 2::FMXRefID

                      Now click on your fields tab and define a field with global storage, gCardSearch

                      Add this field to your layout if FileMaker did not add it automatically and use the Inspector's data tab to set it up as a drop down list. Set up the drop down's value list with the specify field option to list values from YourTable 2::XrefButtons. Select the "include only related values.." option and select YourTable as the "starting from" table.

                      Now, if you enter browse mode and click on this drop down, you'll see the list of values from the current record's XrefButtons field.

                      we can write a script to extract the relevant info from a line selected in this drop down and then perform a find for the indicated record.

                      If [ RightWords ( YourTable::gCardSearch ; 1 ) = Quote ( "index" ) // this is an "index" button reference ]
                         Go To Layout ["Index" (Index Stack) ]
                         Enter Find Mode [] //clear the pause check box
                         Set Field [Index Stack::XrefButtons HCXrefID ;
                                        Let ( w = WordCount ( YourTable::gCardSearch ) ; Middlewords ( YourTable::gCardSearch ; w - 3 ; 1 ) ) ]
                         Set Error capture [ on ] //keeps dialog box from interrupting the script if no records found.
                         Perform Find []
                         If [ get ( FoundCount ) = 0 ]
                            Show Custom Dialog ["No records found for this card reference."]
                         End If
                      Else
                         Enter Find Mode []
                         Set Field [YourTable::XrefButtons HCXrefID ; RightWords ( YourTable::gCardSearch ; 1 ) ]
                         Perform Find []
                         If [ get ( FoundCount ) = 0 ]
                            Show Custom Dialog ["No records found for this card reference."]
                         End If
                      End IF

                      Long Term fix:

                      What you have here are many to many relationships of this table to itself and also a one to many link to the "index stack" table. To facilitate Filemaker type relationships instead of performing a find each time and to make it possible to link new records to existing ones easily will require a "join" tables for each of the many to many relationship and an ID field in the index table to facitiltate the many to many relationship.

                      You'll need relationships like these:

                      YourTable----<XReferences>----YourTable 2

                      YourTable::FMXRefID = XReferences::FMRefID 1
                      YourTable 2::FMXRefID = XReferences::FMRefID 2

                      YourTable----<Index

                      YourTable::FMXRefID = Index::FMRefID

                      With these set up, you can use portals to list related records and also set up drop down lists that list the record titles while selecting a value actually enters the ID number. Linking two existing cards becomes a fairly simple data entry process by using such value lists to enter ID's into the FMRefID 2 field.

                      • 8. Re: Making a text field (edit field) into a drop down list
                        LewisRobinson

                        Thanks -- that's great.  I haven't digested it completely, but a 6 hour wait in the ER for a family member yesterday gave me lots of opportunity for thought.  The one thing about each link (button) in my system, is that each goes to a single unique record (card) which has a unique ID (in both Hypercard) and ultimately in Filemaker.  I'll never need to deal with a find set larger than one.  Also the links are much the way we thought of synapses in the brain until 10 years ago (I'm a retired neurologist)  These are links between neurons -- but they were thought to go just one way (much likethe way that a transistor permits current flow in one direction but not another).   Now we know that information flows both ways across the synapse.  Two of the chemicals thought to be involved in 'retrograde' neurotransmission is none other than the endogenous marihuanas in our brains (anandamide and 2 arachidonyl-glycerol).  I thought for all your help you ought to get something interesting back.  

                        " I'm also guessing here that the end of each line of text is either the card ID for a record in the current table or 'of stack "index"'  -- quite correct

                        I've already used the sort of scripts you describe to pull out words, lines etc, from the way I schlepped data over from HyperCard.

                        Here's what the complete data for this particular card looks like

                        card "X564442"

                        Am. J. Med. vol. 86 pp 481 - 483 '89

                        2

                        card button "Flushing -- differential diagnosis"  go to card id 1409755 of stack "Index"

                        card button "Myopathies with other lipid lowering agents"  go to card id 42854

                        5

                        Lipids and Lipoproteins, Atherogenesis

                        Polymyositis: Dermatomyositis

                        Myopathy due to drugs and non-muscle diseases

                        Stiff man syndrome: cramps

                        Liver Disease

                        2

                              3 cases of myopathy associated with nicotinic acid (niacin).  The flushing seen 1 - 2 hours after ingestion is mediated by prostaglandins, and can be retarded by aspirin prior to nicotinic acid use. It is recommended that hepatic enzyme tests be monitored regularly in all patients on sustained nicotinic acid, rare cases of hepatitis and jaundice have been reported.

                         

                              The myopathy reported in these 3 cases was quite painful.  Thus patients with polymyositis should be asked if they are on nicotinic acid.   

                        Another script takes this info (which was originally put into a text field called XrefHighColonic) and dumps the various parts into the appropriate fields -- one of which you saw -- the button field. 

                        More later -- thanks for all your help. 

                        • 9. Re: Making a text field (edit field) into a drop down list
                          philmodjunk

                          You are on the right track. My use of Finds was just the "quick and dirty" solution to provide some short term utility. Building the join table and ID code based relationships will ultimately replace this method and can facilitate some fairly easy ways to build multiple links bewteen different records to produce the same functionality that you had in your hypercard stack. These links can definitely be bi directional.

                          Here's a demo file on many to many relationships you may want to examine:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                          Here's a demo file of some enhanced value selectio techniques that can facilitate using text patterns to find a record, but then to establish a link to that record based on it's ID number:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

                          • 10. Re: Making a text field (edit field) into a drop down list
                            LewisRobinson

                            Your 2:39 note came in as I was finishing this up

                            After digesting your latest (very helpful) note, here are some responses to it.

                            We need a calculation to extract the Card ID's from the mass of text included with each. You appear to have a list of values separated by returns. In your example, is "2" the first value? What does it represent?"

                            2 represents the number of buttons. It tells the script putting the button information, how many lines (return separated text) to put into the XrefButtons field, before moving on to processing the next group of lines (which get put into the XrefTopics field).  The script doing all this is called "Put Card Data"  -- it's not very long -- only 15 lines or so (excluding the comment following every line).  I can post it if you wish to see it.  It's able to pick out parts of lines, words, etc. etc.  

                            " I'm also guessing here that the end of each line of text is either the card ID for a record in the current table or 'of stack "index"'.  

                            Absolutely right ! ! ! go to card id 42854 means go to another card (record) of this (same) stack  -- which is in this case the Xref stack (table in FileMaker).  While  go to card id 1409755 of stack "Index" means go to a different stack (table) with the stack name made explicit.  

                            So here's my current plan.  Probably the Topics stack (table) won't be required, as I'll just be able to search that field for the particular topic name, using Filemaker's (hopefully) blazing search capabilities.

                            There will be 3 basic tables -- Xref, Index and Glossary.  Each basic table has 3 types of links (buttons)  -- one type to itself and one each to the other basic tables.  There will be a separate table for each basic table called XrefButtons, IndexButtons, GlossaryButtons -- reachable through a portal.  According to the "FileMaker Pro the missing manual' pp. 151 -->  "Creating Records through a portal" , I'll use the information in the XrefButtons field of each record in Xref (and the other two) to create the appropriate record in the appropriate ___Buttons.  I haven't figured out all the fields needed for the ___Buttons tables yet, but they'll include the hypercard IDs of the parent record in Xref, the child record in XrefButtons, the filemakerIDs of these records  etc. etc. 

                            Here is where my understanding of FileMaker is quite incomplete.  I assume I can select any line (record) in the portal, and trigger a script by this selection.  I'm not sure how to do this.  What I'd like the (so far unwritten) triggered script to do is to go the the record corresponding to the selected portal row in the appropriate ___Button table, pick up the other 'half' of the link and go to that record.   I've got no idea how to accomplish this.

                            Clearly I've got some reading to do about join tables etc. etc. 

                             

                            Once again, hanks for you help

                            • 11. Re: Making a text field (edit field) into a drop down list
                              philmodjunk

                              You can place a Filemaker button in the portal row or use button setup... to convert the fields in the portal row into a button. Either way, this button can use the Go To Related Records script step to take you to the related record.

                              There are many, different options to Go To Related Records so it pays to read up on it and to experiment with the various options to see how they work with your system.

                              The Complete Go To Related Record

                              BTW, the times shown on thes posts are specific to your time zone so I don't see the same times on them that you do. Wink

                              • 12. Re: Making a text field (edit field) into a drop down list
                                LewisRobinson

                                PhilModJunk -- once again, thanks greatly for all your help.

                                Way back when Moliere wrote a play "Le Bourgeois Gentilhomme" satirizing the efforts of a nouveau riche individual to masquerade as one of the upper classes.  Our hero hired an instructor to teach him the finer points of language and grammar.  One of his first questions was the difference poetry and prose.  He was amazed to find that he'd been speaking prose all his life.

                                After reading "FileMaker Pro the Missing Manual"  pp. 181 --> about relationships.  What I described in the previous post as the three ___Button tables,  invented out of necessity, are really join tables.  Amazing !    Now to get to work.