3 Replies Latest reply on Jul 6, 2010 3:01 PM by philmodjunk

    Need help with adding new records in a portal

    bate01

      Title

      Need help with adding new records in a portal

      Post

      I am using FM 10 on a Mac X os.

      I have a database where I need to create serial revision numbers to a course id # (course doc db) via a portal (revision db). It is a one to many relationship. It is not working the way I want it to. I need to be able to add a revision to the course id via the portal and have it give me the next serial revision number for the course id # (ex. course id # field - NP333, revsion # field 1 or 2 or 3, etc.). The course id # and revision # are two individual fields.

      Ex. I have a course record that may have many revisions (NP222 rev 1,NP222 rev 2,NP222 rev 3,NP222 rev 4) then I have another course that may have 1 revision (NP333 rev 1). We need to track the history of what was done in each revision.

      Also I need to get a running summary of how many hours are worked on each course and revision. Ex. NP333 rev 1 hours worked 8, NP333 rev 2 hours worked 2 = total hours worked 10. Every time I add a revision to a course I need to total the hourse worked for each revision to the course.

      I hope this makes sense. I know what I want it to do, I am just having trouble getting it to work the way I need to. I am still learning.......

        • 1. Re: Need help with adding new records in a portal
          philmodjunk

          How have you structured your tables? It would appear you need at least two tables, very possibly more:

          Courses (CoursePK, CourseID, CourseName, etc.)
          Revisions(CourseFK, Rev#, Hours)

          I'm adding a new field CoursePK, as a filemaker controlled auto-entered serial number as I suspect that your course ID's are typed in and that makes them a less than optimum choice for linking your two tables.

          Link them like this: Courses::CoursePK = Revisions::CourseFK and enable the "allow creation of records..." for Revisions

          Define a calculation field in Courses as cMax : Max ( Revisions::Rev# )
          Define a calculation field in Courses as cTotalHours : Sum ( Revisions::Hours )
          Define Rev# with an auto-entered calculation as Courses::cMax + 1

          Place a portal to revisions on your Courses Layout and when you enter data for a new revision in the bottom blank row of the portal, your revision# field should increment correctly to show the next revision number.

          Note: This method can fail if two people try to log a new revision for the same course at the same time if the database is shared over a network.

          • 2. Re: Need help with adding new records in a portal
            bate01

            Thank you very much. I had most of it set up like you suggested so at least I was on the right track. You were correct about the admin typing in the course id #.

            I may be confusing myself now but I am still having an issue with it generating the rev #s correctly. It is almost there though. How do I get the rev # to be serial based on the course id #? I need the numbers to automatically start at 1 for each course Id  number then count up from there. (ex. NP333 rev 1, NP333 rev 2,  then for the next course # NP222 rev 1, NP222 rev 2, etc.)

            • 3. Re: Need help with adding new records in a portal
              philmodjunk

              "How do I get the rev # to be serial based on the course id #? I need the numbers to automatically start at 1 for each course Id number then count up from there."
              I've been testing my suggested auto-enter calculation and it's not working like I thought it would. The value in cMax does not appear to update correctly before the calculation grabs and increments it. I'll keep playing with it but perhaps someone else can suggest a fix in the mean time.