2 Replies Latest reply on Mar 30, 2013 1:36 AM by FMNewbie

    Portal Calculation Alternatives??

    FMNewbie

      Title

      Portal Calculation Alternatives??

      Post

           Hi there... I know that placing calculations in fields that appear in portals can be a little unreliable so I'm looking for a better way to do this.  

           I want to create a database to document specimine growth including photos.  I'm assuming the best way would be to put the photos in a container field on a separate related table (call it PHOTOS).  Then in another table I would place all the individual data (call it DATA) for that particular specimine (ID, start date, end date etc).  

           Ultimately what I need to have is a layout where I can compare only specific specimines (which is where the portal comes in I think) and have it display the appropriate photo and data for the comparison date entered on that layout.  My thinking was that this layout needs to be on a third related table... call it COMPARISON.  

           I first tried this by listing the specimines I want to compare in a simple repeating field on COMPARISON which would trigger the appropriate specimine data to pop up in the portal rows from the related tables.  

           The problem arises when trying to get the right photo to populate.  I was using a repeating field for the container, and then trying to use a calculation to select the right repetition to display in the portal row.  The problem is that it does this calculation correctly sometimes, and not others, and sometimes not at all.  

           I'm thinking the better way to do it is have each photo as an individual record and have the portal on the comparison layout somehow grab both the correct photo from the PHOTOS table, and the correct data from the DATA table.  I think I may be looking at using the LOOKUP function but I've never used it.  

           Anyway... hopefully that's not too convoluted.  Before I rebuild the whole database, I'm hoping for a little guidance on my database design.  

           Cheers.... 

        • 1. Re: Portal Calculation Alternatives??
          philmodjunk
               

                    I know that placing calculations in fields that appear in portals can be a little unreliable so I'm looking for a better way to do this.

               That is not necessarily the case. Calculation fields usually work just fine in a portal row.

               

                    I'm assuming the best way would be to put the photos in a container field on a separate related table (call it PHOTOS).  Then in another table I would place all the individual data (call it DATA) for that particular specimine (ID, start date, end date etc).

               If you have one record in DATA for each photo in PHOTOS, then there is no need for two tables unless you need to use Import Records | Folder to pull the photos into your database to match up with existing records in DATA.

               If there are many records in DATA for one record in Photos, you can use a layout based on PHOTOS with a portal to Data to display the associated data. IF there is one record in DATA for many PHOTO records, the reverse can be done.

               Under no circumstances should you try to use a repeating field for this purpose.

          • 2. Re: Portal Calculation Alternatives??
            FMNewbie

                 There would be several records for PHOTOS for each record in DATA.  Each specimine would have several photos documenting phases of growth over time.   Thanks for the answer, I'll try building it that way.