10 Replies Latest reply on Jul 17, 2011 4:59 AM by Corné

    Creating a new record in a portal?



      Creating a new record in a portal?


      Hello all,

      In my database with customers I would like to keep a log of calls etc. Therefor I created an extra table 'logs' which I related to my table 'Customers'. With a portal with 1 line I keep a log text per day. So far it is working, but I cannot seem to find a way to create records for new logs. And since there is just one portal line, I suppose it's a reason I do not get an empty line to add extra records?



        • 1. Re: Creating a new record in a portal?

          Did you add a scroll bar to your portal so that you can scroll to the last portal row?

          Alternatively, you can right a script to create a new blank record in the portal's table when you click a button on this layout. If you set up the right portal sorting so that the newest record sorts to the top of this one row portal, clicking the button will create a new blank portal row that you can then use to log your data.

          Here's a typical script for creating a new related record:

          Set Variable[$PrimaryKey ; Value: YourLayoutTable::YourPrimarykeyField]
          Go To Layout [//select a layout based on the log table]
          New Record/Request
          Set Field [YourPortalTable::yourForeignKeyfield ; $PrimaryKey]
          go to Layout [original layout]

          • 2. Re: Creating a new record in a portal?

            Thx for your input Phil!

            I did not add a scroll bar to the portal, I liked to go for the script option.

            I guess your script is working well to just create a new record and I guess that's exactly what I was asking for. Perhaps what I meant was that the new log should be related to the customer currently shown. I guess that not just a relationship should be made with a CustomerSerialID and the foreighn CustomerSerialID, but with a LogID (local and foreign) as well, no?

            • 3. Re: Creating a new record in a portal?

              The script uses the variable to maintain that relationship. I don't know your fields or the relationship so referred to "primary key" and "foreign key" here in the steps that ensure that the new related record is related to the current record in your original layout and thus will appear in your portal.

              • 4. Re: Creating a new record in a portal?

                Well, let's say I've got these two tables:

                Customers (CustomerID, First name, Surname)
                Logs (LogID, CustomerID, LogText)

                Now I have this relationship: Customers::CustomerID = Logs::CustomerID (is that correct?) A portal of Logs exist in the Customers layout, and records are show from the Customers Table.

                I'm not 100% sure about translating your script example to my situation?

                • 5. Re: Creating a new record in a portal?

                  Set Variable[$PrimaryKey ; Value: Customers::CustomerID]
                  Go To Layout ["Logs" (Logs)]
                  New Record/Request
                  Set Field [Logs::CustomerID ; $PrimaryKey]
                  go to Layout [original layout]

                  In the case of your tables, CustomerID is the Primary key for customers and a foreign key for Logs.

                  • 6. Re: Creating a new record in a portal?

                    Hm, I see that I interpreted your example correctly. Why I was double checking is because the new blank record is not show in my customers table. Should I refresh something, or how can I make the new log record up front in the portal?

                    • 7. Re: Creating a new record in a portal?

                      You've missed part two of my suggested solution. You need to use portal setup to sort your portal records so that the newly created record sorts to the first row of your portal. There's any number of ways you can sort to get that result. Given the fields you've mentioned, sorting them by LogID in descending order will do the trick.

                      • 8. Re: Creating a new record in a portal?

                        That's correct, thank you! Reading all text seems to be a challenge ;-) Now I indeed have a blank record up front. That in combination with the scroll bar is an interesting touch on scrolling through the logs for that customer. Suppose I do not want to scroll, but like to skip through the logs with "+1 or -1" buttons? A simple "next record" won't do the trick, 'cause the next record in the logs table might be connected to another customer, right?

                        • 9. Re: Creating a new record in a portal?

                          It can be done with a bit of scripting slieght of hand.

                          Set up a script trigger with OnRecordLoad to perform this script:

                          If [Not $$TriggersOff and Logs::LogID //I'm assuming LogID is of type number here ]
                             Freeze Window
                             Go To Related Records [Show Only Related Records; From Table: "Logs"; using layout: "Logs"]
                             Sort [No dialog ; Restore] //Sort in descending order by LogID just like your portal
                             Set Variable [$$LogID ; Value: Logs::LogID]
                             Go To Layout [original layout]
                          End If

                          Set up your portal to use this portal filter:

                          Logs::LogID = $$LogID

                          Now your "next" button uses this script:

                          Set Variable [$$TriggersOff ; Value: True]
                          Freeze Window
                          Go To Layout [Logs ( Logs )]
                          Go To record/request/page [next]
                          Set Variable [$$LogID ; Value: Logs::LogId]
                          Go To Layout [original layout]
                          Set Variable [$$TriggersOff ; Value: False]

                          Your "previous" button uses the same type of script bu you use the Previous option in Go to Record instead of "next".

                          The $$TriggersOff variable keeps the first script from interfering each time you click a next or previous button.

                          Note: This method requires that the found set of Log Records not be modified by any other script or user action. Sometimes this requires setting up a special table occurrence of the portal table on a hidden layout, though this doesn't appear to be the case here. Also, this only works in FileMaker 11 due to useing a portal filter. A slightly different approach using a global field in place of $$LogID and as part of the portal's relationship can be used with FileMaker 10 to get the same results.

                          • 10. Re: Creating a new record in a portal?

                            I am glad that I didn't ask a too obvision question, I thought that it coudl be done with a simple script item I just missed. I will try if it's working for me, got no doubt though, your suggestions thusfar have been helping me great!