5 Replies Latest reply on May 12, 2017 3:55 PM by keywords

    Who's Up Next??

    rickaltman

      I am creating an app for a conference. Each seminar is assigned a code -- such as 101, 102, 103, etc. -- and the app will automatically create signage and displays for us. One of the services I want to provide is to tell people not only what seminar takes place right now, but which seminar will be *next up* in a particular ballroom. I have created a field called Next, into which we would enter the code for the seminar that takes place next, but that's as far as I have gotten. How do I automatically gather the information?

       

      For instance, let's say that Seminar 101 is entitled "How to Think," and is being led by John. Next up in that room is Seminar 103, entitled "How to be Great," led by Jane. I want to create a calculated field that would be part of 101's record that would automatically pick up title and speaker from 103's record.

       

      How would I do that??

        • 1. Re: Who's Up Next??
          TomHays

          You'll need to be careful on the order of data entry.

          You create record 101.

          It has a field called Next that should hold the value 103 or whatever number is to be next.

          But it can't hold the value 103 since 103 doesn't exist yet.  That record has not yet been created.

           

          So you'll need to create all of your records and then go back over them and enter the Next value.

           

          To fill in the values for Next after all records are created, you could sort the records by Code and do something like this calculation:

          GetNthRecord(MyTable::Code; Get(RecordNumber) + 1)

           

          This could be applied by a Replace Field Contents... with Calculation.

           

           

          An alternate method that builds in the feature to calculate on-the-fly and does not need you to do the data entry ahead of time is that

          you could set up a relationship between the two

          MyTable::Code <------=-------> MyTable__Next::Code

          where = is defined with Code < Code and the records for MyTable__Next are sorted ascending by Code

          Then a calculated field defined as

          MyTable__Next::Code

          will be the value from the next higher record.

           

          If you are so inclined you could also do this calculation using ExecuteSQL().

           

           

          -Tom

          • 2. Re: Who's Up Next??
            rickaltman

            Hello, Tom --

             

            Allow me to elaborate: all of the records have been created. We know what every seminar is and all of their basic information exists. So yes, the record for Seminar 103 exists, the Next field for Seminar 101 contains the value 103, and the calculation field entitled NextDisplay has been created, as well.

             

            Now I need to fashion some sort of calculation that knows how to pull the values from the record identified in the Next field and make them part of a calculation in the NextDisplay field. Any ideas?

            • 3. Re: Who's Up Next??
              TomHays

              If I understand the situation now, you already have the Next field populated with the correct values.  Now you want to display additional fields from the record referenced by Next.

               

              That is done with a Relationship.

               

              In the Manage Database-->Relationships you need to set up another Table Occurrence for your table.  I'll call the new Table Occurrence "MyTable__Next".

              Then connect

               

              MyTable::Next <-- = --> MyTable__Next::Code

              where the = is equality between the two fields.

               

              Now you can put onto your layout or use in calculations Related fields that start with MyTable__Next:: such as

              MyTable__Next::Title

              MyTable__Next::Leader

               

              -Tom

              • 4. Re: Who's Up Next??
                rickaltman

                Thank you very much. This is a bit ambitious for me (I'm much better with calculations and scripts than I am with relationships), but I will give it a go...

                • 5. Re: Who's Up Next??
                  keywords

                  How is your database structured? If you have your venues in their own table (and it seems to me you should) with join tables to link them to the events that will run in them, then it is a simple matter of generating a venue report in order to list the events that will take place in any given room.