1 2 Previous Next 22 Replies Latest reply on Jan 12, 2010 6:46 AM by Weeds

    Displaying different information in a field

    Weeds

      Title

      Displaying different information in a field

      Post

      I am working on a project that requires relationships with multiple tables. The key to making all of them work is through a unique entity id number. My question is, can a field house data but display something different?

       

      I would like a field to store the entity id number so it can relate to another table but want it to display the person's name instead of the number. Can you do that?

       

      My knowledge of Filemaker is slim, so please break it down for me if you can figure it out.

       

      Thanks! 

        • 1. Re: Displaying different information in a field
          ninja
            

          Howdy Weeds,

           

          Your UniqueID field doesn't have to be on the layout...leave it off.

          Put the name field on the layout.

           

          The relationship is not based on the fields showing on the layout...it is based on the fields existing in the table.

          Showing the name field and not showing the UniqueID field will have no effect on the relationships.

           

          {If you're actually asking about choosing a name from a drop-down menu and having the field populate with the ID# (my guess and not written in your post), look at value lists, 'also using info from a second field', 'using ONLY values from the second field'.}

          • 2. Re: Displaying different information in a field
            Weeds
              

            Alright maybe I should explain my full problem in why I need this. I am trying to create a seating database for an event. The event has 500 people attending and each have a bio (created from a different department in a separate database). Each individual is seated at a table and given a table number and seat number. One long, rectangular table seats 36 people--18 on each side.

             

            My dilemma is that I want to create table diagrams that auto populate. After assigning someone a table number and seat number I want their name to appear around the table. I would then like their name to link to their bio in a separate database. I figured id numbers was the best way to make them all relational.

             

            In the end I want to be able to print off each individual table to use as a seating diagram. I do not need to see all 30 something tables at once.

             

            Let me know if this makes no sense at all.

             

            Many thanks.

            • 3. Re: Displaying different information in a field
              philmodjunk
                

              I'm guessing a bit here from your description, but I think you can do what you want by setting up a two column value list where column 1 is the ID field and column 2 is the name field. Hide the first column. Format your field as a pop-up menu and you'll be able to click on the field to select a person by name, but the ID number will be entered into the field. When you exit the field, pop-up menu formatted fields with this set up will continue to display the name instead of the number.

               

              That what you had in mind?

              • 4. Re: Displaying different information in a field
                Weeds
                  

                This can definitely work because now it is housing the two data information that I want. However, is there a way to write a script so it can autopopulate this? Let me break down the two tables for you and how they are relational.

                 

                Seating- Contains all 500 records (individuals) that house their ID number and name. Here they are assigned a table number and seat number

                 

                Table- Records are the table numbers. I created a two value column named TableSeat that will house the ID number from seating but display the name from seating.

                 

                The two tables (seating and table) are relational via table number. Is there a way that I can write a script that does a find for the table number and seat number in seating but ultimately displays the name in the TableSeat field in the Table table.

                 

                I hope that is not too confusing. I am working on writing a script write now but I am having difficulties getting the name to appear. Any help would be much appreciated but the two column value was a great idea-- I never knew you could do that, so thank you.

                • 5. Re: Displaying different information in a field
                  philmodjunk
                     As Ninja said, Once your find has used seat number and table number to find a specific record. The layout can include the name field from the related table so that you can see the name. The number fields need not even be present on the layout used for the find as long as the layout refers to the correct table.
                  • 6. Re: Displaying different information in a field
                    Weeds
                      

                    Right that is fine. I am stuck though on the last step of the script. I made it do a find and then went to the table layout, now how do I get the field TableSeat to display what I want. Originially I tried it by setting the field but that is not working.

                     

                    I realize this is hard to explain because you cannot see my script.

                     

                    If anyone knows that is great because this is all that is stopping me from completing this project.

                     

                    Thanks!

                    • 7. Re: Displaying different information in a field
                      ninja
                        

                      Howdy,

                       

                      I keep trying to come up with a prettier way, but keep getting pulled away before the "Aha, it's simple" moment shows up.

                       

                      I can see it {brute force} working with:

                      With 1 record per table in the "Table" table.

                      50 SeatNumber fields in the "Table" table.

                      50 text calc fields "TableSeatNum" (TableNum & "-1", TableNum & "-2",...) in the "table" table.

                      and 50 T.O.'s of the "Individuals" table linked in, one to each text calc field.

                       

                      but there has GOT to be a simpler way.

                       

                      Assigning the people to a table and seat# is pretty direct.  It's the printing of a seating chart that pushes it out to 50 different fields for the 50 relationships (uggh).  But listing the 50 individuals in a "this table seats these people" portal doesn't give the seating diagram.

                      • 8. Re: Displaying different information in a field
                        Weeds
                          

                        That is exactly the dilemma. Portals don't work for two reasons-- it's hard to create a pretty diagram and when a seat is empty it gets messy (because not all tables are full).

                         

                        If I can write a script for one individual to one individual seat then I do not mind duplicating it all for 500 people. The effort that I put into it now will save me hours on end in the long run and this whole database can then be duplicated for other events and multiple uses.

                         

                        If you have any thoughts on writing the script, please let me know. I don't know too many of the functions so I am going to look into get and variable functions within a script to finally get the name to appear in the field.

                         

                        Many thanks to both of you.

                        • 9. Re: Displaying different information in a field
                          ninja
                            

                          Weeds,

                           

                          I posted the {brute force} idea for two reasons.

                           

                          Firstly so that someone slicker than I at design might critique it and show how it could be made simpler.

                          Secondly so that, if no one does critique/refine it, you could brute force your way through and end up with what you want.

                           

                          Note that the method does not rely on scripting at all.  It uses relationships to tag people to seats.

                          Note also that this design is work to scale...if your next event has 60 people per table, or 20, it'll require design tweaks.

                           

                          If you post the current version of your script, and show where it falls down, it may jog a few suggestions as well.  Maybe you've thought of a good way that I haven't yet and it just needs the last tweak.

                          • 10. Re: Displaying different information in a field
                            comment_1
                              

                            You need (at least) three tables here: Tables, Seats and Individuals (I say at least, because this arrangement will cover one event only).

                             

                            After assigning each individual to a seat, you can show a "pretty diagram" by placing a portal to Seats on a layout of Tables, and breaking it up into individual rows. Place the field Individuals::Name inside the portal. No scripts are required for this.

                             

                             

                            • 11. Re: Displaying different information in a field
                              Weeds
                                

                              Okay this is what I have so far:

                               

                              In the seating database, a record is given a table number and seat number.

                               

                              In the table database, a record is an individual table. I set up 18 fields on either side of the rectangular table and named them Table1Seat1, Table1Seat2, etc. These fields are a drop down menu of all the records from the seating database and list their name/id number but display their name only.

                               

                              The two databases are relational by table numbers.

                               

                              What I want to create is a way that when I assign someone to Table 1 Seat 1 their name will apear in Table1Seat1. And so I wrote a script:

                               

                              Go to layout "Seating" 

                              Perform Find (Seating::Table Number [1] AND Seating:: Seat Number [1])

                              Go to layout "Table 1"

                              Set Field (Table:: Table1Seat1; Seating:: Name)-- this part is not working. How do I get the drop down value list to "pick" the right individual to display.

                              • 12. Re: Displaying different information in a field
                                ninja
                                  

                                Understanding that there are two different approaches under discussion ("Related" vs. "Scripted").

                                 


                                Weeds wrote:

                                 

                                Go to layout "Seating" 

                                Perform Find (Seating::Table Number [1] AND Seating:: Seat Number [1])

                                SetVariable [ $Name ; Value: Seating::Name ]

                                Go to layout "Table 1"

                                Set Field (Table:: Table1Seat1; $Name)


                                 

                                I think this is what you're looking for for the Scripted approach.
                                • 13. Re: Displaying different information in a field
                                  ninja
                                    

                                  @ comment,

                                   

                                  That's one approach I thought through.  Picturing the layout, however, I didn't see how to make the seating chart work.

                                  Two portals with 25rows each and having seat#s 1L, 1R, 2L, 2R?

                                   

                                  How do you envision a printable seating diagram of a rectangular table with seating on both sides?

                                  • 14. Re: Displaying different information in a field
                                    Weeds
                                      

                                    I don't think I can swear on here but the script worked! It actually worked! Thank you, thank you, thank you for solving my many dilemmas.

                                     

                                    Now all I need to do is hyperlink their names around the table to their bio. This is so incredible. I can't thank you all enough.

                                    1 2 Previous Next