3 Replies Latest reply on Mar 28, 2014 7:03 AM by philmodjunk

    Trouble with portal and table relationships

    arronman

      Title

      Trouble with portal and table relationships

      Post

           I have tables "Table" and "Shops". They are related via 2 fields ("Shop Name" and "Suburb")

           I have a layout which displays fields from "Shops" and contains a portal with fields from "Table". Creating a new record within the portal works, however I need for the data in field "WW" to be automatically copied, - as well as Shop Name and Suburb - but this doesn't happen.

           I tried adding a third relationship:- Table::WW to Shops::WW, however the portal no longer displays records data correctly or completely. The portal returns to normal when the 3rd relationship is removed.

           What am I missing? What can I do?

        • 1. Re: Trouble with portal and table relationships
          philmodjunk

               Are WW, ShopName and Suburb fields defined in Shops?

               If they are defined in Shops, why do you need to copy them to Table? (Why not name "table" "Visits"?)

               Given the relationship between Shops and Table, you can use that relationship to display the value of WW, Shops and Suburb without ever needing add fields to Table for that purpose, you can just use the fields from Shops.

          • 2. Re: Trouble with portal and table relationships
            arronman

                 Thanks for reply (Iv'e been very busy doing different stuff).

                 WW, ShopName and Suburb fields were originally defined in 'Table", but any new shop records created are defined in "Shops"

                 Reason I think need to copy field data to "Table" is that I use a portal to display visit data from "Table" in a layout.

                 Second reason is that old visit data is contained in "Table", so I decided to create a new visit within the portal mentioned above, but the new visit record is created in "Table"

                 Third reason is adding complexity to existing reports which access "Table" only.

                 Reason 4: Sometimes want to view/sort all visit data.

                  

                 And yes, I'm fed up with "Visits" being named "Table"! Been lazy to edit scripts if I rename. Will change it to save my sanity. And yours ...

            • 3. Re: Trouble with portal and table relationships
              philmodjunk
                   

                        Reason I think need to copy field data to "Table" is that I use a portal to display visit data from "Table" in a layout.

                   With the correct relationships in place, you can still include fields from a table occurrence of Shops in the portal row.

                   

                        Second reason is that old visit data is contained in "Table", so I decided to create a new visit within the portal mentioned above, but the new visit record is created in "Table"

                   That looks a bit "twisted around" to me, but it may be in how you have described it. If you need to keep historical copies of the data in this field, one normally keeps the data in the parent record (Shops) up to date and sets up corresponding records in the child (Table) record that copy data from the parent each time that you create a new record. To see past versions of this data, you review past records of past visits to see what was current at the time. There are auto-enter field options that you can set on fields in Table in order to copy data from Shops each time that you create a new record in Table and link it to a record in Shops.

                   

                        Third reason is adding complexity to existing reports which access "Table" only.

                   Can't see where this adds much complexity to your reports and it can make managing this data MUCH simpler.

                   

                        Reason 4: Sometimes want to view/sort all visit data.

                   Don't see where that will be a problem.

                   

                        Been lazy to edit scripts if I rename.

                   With a very few exceptions, renaming a field, table, or table occurrence in manage | Database will not require updating calculations or scripts. The few exceptions are those script steps where you have entered that name and had to type in quotes to enclose the name as literal text. Set Field By Name (script step), ExecuteSQL() (Function) and GetField (function) are the three such examples that come to mind. And you can often set up these two items to not use quoted text field references in a manner that enables them to also update when a name is changed.