6 Replies Latest reply on Aug 27, 2010 2:29 PM by DanParadis

    Reading from a System Table using as a template

    DanParadis

      Title

      Reading from a System Table using as a template

      Post

      OK, I have a simple system table that has an expense desc, hourly rate and flat rate, called Expenses.

      I created a layout with a portal that allows you to choose from the system table Expenses into an account level table called Event_Expenses.  That all works fine.  Now I want to extract the hourly rate and flat rate from the Expenses system table and initialize the Event_Expenses with their values.

      What's the best way to go about doing this?  Is there good documentation somewhere on how to use related tables and their values?

      Thanks.

      Dan

        • 1. Re: Reading from a System Table using as a template
          philmodjunk

          Check out these two auto-enter field options: Looked up Values and auto-entered calculations. Either can be used to copy a value from your expenses table into a corresponding field in your Event_Expenses table each time you select an Expense from the Expense table.

          • 2. Re: Reading from a System Table using as a template
            DanParadis

            Hmmm...  Seems simple enough.  I expected it to work, but it's not populating the table.  I'm sure it's something I'm doing.

            Maybe it is setting the default, just not populating the screen display.  I'll check that now. 

            Thanks!

            Dan

            • 3. Re: Reading from a System Table using as a template
              philmodjunk

              The key is the relationship linking your two tables. Do you have such a relationship?

              If you have a relationship like:

              expenses::ExpenseID = Event_Expenses::ExpenseID

              Selecting an ExpenseID in Event_Expenses::ExpenseID will trigger the Looked up value action.

              • 4. Re: Reading from a System Table using as a template
                DanParadis

                Yes, I have a relationship between the 2 tables.

                Is there a timing issue?  For example the blank event_expense record is created in the portal row.  Then I select the expense table choice.  Would it know whenever the id/desc is selected to do the calculation?

                Dan

                • 5. Re: Reading from a System Table using as a template
                  philmodjunk

                  It should happen immediately, this is the standard approach used in an invoicing system to lookup unit prices from a price list table. You enter/select the product ID and the matching unit price for the item instantly pops into the field.

                  "Portal row" conveys new information.

                  You indicated you had two tables, Expenses and Event_Expenses. That suggests a third table which might be a factor. Exactly what tables are involved here and exactly how are they related?

                  What table is your layout based on?

                  What table is the portal based on?

                  Is there a relationship between the Portal's table occurrence and the Expenses table?

                  A table occurrence is the name of a "box" in Manage | Database | Relationships and the "show records from" box in Portal setup should list a "box" in Manage | Database that is linked to a Box for Expenses.

                  • 6. Re: Reading from a System Table using as a template
                    DanParadis

                    Thanks, I'm going to check this out tomorrow.

                    BTW, the third table is: Events

                    The portal is based on: Events_Expenses

                    Is there a relationship between the Portal's table occurrence and the Expenses table?  Yes, but there could be something messed up here.  I look at the key ID in the Events_Expenses table and is should be an integer but is the description value of the Expense (i.e. instead of 82 it is "Manager").  Very weird.  I screwed something up!

                    Thanks again for you help!

                    Dan