13 Replies Latest reply on Aug 23, 2012 6:10 AM by philmodjunk

    Replacing Values in a Portal



      Replacing Values in a Portal


      Hi All,

      I have a Few Questions.

      Just wondering if it was possible to get filemaker to replace values in fields in a Portal. I am a scorer for horse events and in a Cross Country Phase of an ODE we are given the faults at each fence. At the moment, in access, we just type the rider number in a field, then the fence number and then the faults above a portal containing a detailed overview of the jumps for each competitor, please see the screenshot to see the concept. Is there a script that i can assign to the enter button to do this for me. Note that I have two tables, one for the class and one related table for the competitors information. The layout is based on the class table and the portal shows the information from the competitors table, including the jumps. Note also that the Jump fields are named CCF1, CCF2 etc. up to CCF35.

      Also is there a way for the system to recognise that a character represents a value. At the moment X represents a refusal eg X for a Refusal(20 Penalties), XX for 2 refusals (60 Penalties) and E for the third refusal which incures elimination. That is for the refusals at one jump, I also want it to recognise refusals at different jumps. This is because the first refusal at any jump is 20 penalties, the second & third is 40. The 4th refusal at different jumps is elimination. For clarification you are eliminated for 3 refusals at one jump but are allowed 4 refusals at different jumps overall, before elimination. Is there a function that I can use that will take this into account.

      Finally, I need a button and script to default the jumps to 0 penalties to make entry easier. But only up to the last jump which I will set in a field eg 24 is the last jump. At the moment I have set 35 jumps but I want to be able to set the final jump. This is so the script only clears the jump up to the final and I also want to have a printout that show all of the jumps up to the final, I dont want it to display any jumps that arent used at that time. Eg if I set 24 as the last jump, I only want the first 24 jumps to be cleared by the script and put on the printout. Is this Achieveable?

      Thanks, I have done a lot of googleing and still can seem to find an answer to these questions!


        • 1. Re: Replacing Values in a Portal

          I don't see a portal on your layout. I see 35 individual fields. Unless those are 35 one row portals or something which is indeed possile.

          So the user enters data into the Fence and penalty boxes, then clicks the enter button? (FileMaker can detect when the enter key is pressed if you want to detect the enter key) And then what should happen on the screen? What do the numbers 1 - 35 represent? 35 different jumps for a given contestant?

          • 2. Re: Replacing Values in a Portal

            I am a technician and not a Filemaker Wizard.

            Any time you have multiple fields in a main table for instances of the same thing, the best solution is a new table.

            If you have a Contacts table and you have a Phone field, then add a FAX field, then add a Home Phone field, then Cell Phone, it suggests  a second table named Phones.
            The Phones table would be related to Contacts table by an ID relate field. Phones would have a PhoneLabel field for  "Main, FAX, Cell, Work, etc".

            The advantage is that  there can be a variable number of Phone Entries for a Contact - one or ten.

            A Jump is created for a competition and a specific Horse - Options for that new field allows Auto-Enter Data to enter 0 (zero).

            You have Events which would be a Table - Events have many Horses
            You have Horses which would be a Table - Horses have many Events.

            Many to Many relationships are to be avoided.
            A Join Table is needed called? EventHorses.

            What do I need to know to work with many-to-many relationships in FileMaker Pro?
            Answer ID: 9922
            Last Updated: Oct 04, 2011

            • 3. Re: Replacing Values in a Portal

              It is a portal with 35 individual fields, Its just only got one record as an example.

              The user in essence has two options to enter the penalties. When a rider has a refusal they can put the rider number (Unique) into the little box at the top along with the fence no. and the penalty incurred, what i want to happen is that pressing enter will put the penalty incurred in the correct record and jump field. ie Rider 1 has a refusal at jump 4, I want to be able to put that in that box at the top and have the system add the refusal to riders record in the Jump 4 field.

              The second way would be to scroll down the portal to find the rider and then select the jump. This would take longer but is a better visual representation.

              Having both allows us to input it quickly whilst seeing whats happens

              Number 1 -35 represent different jump, however the amount of jumps depends on the Cross country course and the class of riders

              Does this clarify it any better?

              • 4. Re: Replacing Values in a Portal

                Hi David, Thanks for your response.

                Just to be clear this database is just for one single event, an ODE, a phase of this event is the Cross Country, As part of the cross country there are numerous jumps, those are represented by the fields in the screenshot. The aim is to jump each one without refusing, The jumps are not the same thing. And at the end of the event people want to be able to see which jumps they had problems with, thats why there is a field for each individual jump and why I want it to idicate where the rider has jumped a jump clear. 

                Hope this helps explain it - this horse stuff can be a bit confusing!

                • 5. Re: Replacing Values in a Portal

                  I agree with David that you should have one related record for each jump, by each competitor. Doing so simplifies your processes, can work from nearly the same layout appearance and people can still see which jumps got refusals and which didn't (as well as the scores for each).

                  Not having the full picture of all you need, I can't give you the complete set of tables and relationships, but for what you have here, I'd recommend that you set it up like this:


                  Competitors::__pkCompetitorID = Jumps::_fkCompetitorID

                  One field in Jumps would be the jump number and another field would be the score. A numer field named "refusals" to count the number of refusals is needed. A summary field that computes the total of Refusals can tell you the total refusals for a given competitor so you know when elimination has occurred and a separate field for penalties can be included if that's useful.

                  Your horizontal rows of competitors and jumps can be created with a list view layout and a series of one row portals. A bit of scripting would be needed if the user does not always enter data for every jump in sequence, but it's not a complicated script should that be needed.

                  Your script for using the scoring box in top left would work like this:

                  Make the three fields fields with global storage specified.

                  Clicking enter would perform this script:

                  Freeze Window
                  go to layout [jumps]
                  New Record/Request
                  Set Field [Jumps::FenceNo ; Globals::gFence ]
                  Set Field [Jumps::_CompetitorID ; Globals::gNo ]
                  Set Field [Jumps::Refusals ; Case ( PatternCount ( Globals::gPenalties ; "E" ) ; 3 ; PatternCount ( Globals::gPenalties ; "xx" ) ) ]
                  Set Field [Jumps::score ; case //I can't specify the cald for this as I don't know the full format of possible scoring/penalty data that might be entered here]
                  Go To Layout [original layout]

                  • 6. Re: Replacing Values in a Portal

                    Great, I Have decided to use only that script you have given me instead of the other method, for the sake of time at an event most people would go that way anyway. But what about the auto creation of records? If I have a field classes with the final jump can I script a button to create records in the Jumps table for each of the riders in that class at each of the jumps.

                    Also how would I go about printing this out? Could you maybe elborate more on the "list view and series of one row portals". Im still fairly new and trying to find my way around.

                    I also made the summary field - How does this field know to only get the total refusals from just one rider?

                    Could I also confirm what you would have in the score field. From what I can gather it is to show the total penalties resulting from the refusals.

                    • 7. Re: Replacing Values in a Portal

                      Note that the script creates the record in jumps for you. But if you enter data in a portal to Jumps, auto-create can still be used, but only if you enter at least some data in each of the preceding jumps for that competitor.

                      You shouldn't need a script to create records in jumps until the moment you record data on that jump and maybe not even then.

                      There are many options for printing this out. A list view layout based on Jumps is one option, you can sort by competitor ID, then by Jump to get the data in proper order. It's also possible to use the same layout you used for data entry to get rows and columns of jump data.

                      A one row portal is a portal with one row. If you check portal setup, you can specify an initial row of 1, 2, 3 for each portal in succession. This allows you to set up a horizontal row of portal records instead of the usual vertical orientation. You can also set a portal filter on each portal that filters for a specific jump number--this might be a better option if you don't enter data for a "clean" jump.

                      A summary field from a related table--such as jumps will report the total of all related records. So if you place this field on your data entry layout--which is based on your table of competitors, each row will show the total for that competitor.

                      I can't really suggest more for the score field as I am very unfamiliar with how you score such an event. I can tell you that what you want is quite possible to do, but the details require understanding how you would record and compute scores "on paper" first.

                      • 8. Re: Replacing Values in a Portal

                        Usually we get sheets in from each jump after the class. We only ever put in riders and jumps for people with refusals. So if a person goes clear and has not refusals then They may not have any records in the jump table, which is why I would have a script to create all of those records as I want the printout to show the people that went clear as well. 

                        • 9. Re: Replacing Values in a Portal

                          Also the main way we score is based on refusals. We First assume that they have all gone clear. Then we go through the individual sheets from each jump, in order, to find any refusals or eliminations. When we do, the resulting penalties are put into the computer. I guess the only thing in the score field would be the resulting penaties from the refusals, So 1st - 20, 2nd - 40, and elimination. These penalties compound on each other eg 2 refusals will incur 20 penalties for the first plus 40 penalties for the second, making 60 penalties for the 2 refusals 

                          • 10. Re: Replacing Values in a Portal

                            I have Put in the script and have a few problems. Firstly is there a way to stop the user entering multiple entries for the same jump. When I put in the number, fence number and the refusals and clicked the button, it worked but there was no feedback, Id imagine a normal person would click a few times and thus we would have multiple records. Is there a way to stop this? 

                            Also I asked the summary field to count the number of refusals and it counted the number of refusals for all riders on all jumps, Have I done something wrong? 

                            • 11. Re: Replacing Values in a Portal

                              The feed back should be that the score or penalties appears in the one row portal for that rider's jump. You can also set up a number of different safe guards to prevent them from entering the same combination of rider and jump more than once. One simple way is to define a text field with this auto-enter calculation: JumpNumber & " " & RiderID. Clear the do not replace existing values check box and select the "unique values" validation rule. If the official attempts to enter a penalty for the same jump and rider, a validation error message occurs, and the user can enter new data and try again. There are ways to trap for this error in your script and revert the new record--popping up a custom message that tells the user why and to try again.

                              It will depend on your layout design as to whether you need a blank record for every jump. Some layouts--using one row portals to arrange the jump data into columns with the riders listed in rows, will not need the blank records. Others, such as a list view summary report, might benefit from such. A script can easily create records in Jumps with the appropriate jump number and rider ID.

                              Also I asked the summary field to count the number of refusals and it counted the number of refusals for all riders on all jumps, Have I done something wrong?

                              As I previously posted, it depends on your layout design, found set, and relationships. If you have this relationship:


                              And your summary field is defined in Jumps.

                              Place the summary field on the Competitors layout and it will report a summary value for all the Jumps records for each competitor. If you place it on a Jumps layout, you'll see a summary value based on all records in your found set---which could be all the records in your table or just the records for one competitor.

                              • 12. Re: Replacing Values in a Portal

                                Ok so what I have done is put a relationship between the rider numbers in the Jumps table and the competitors tables. I want a Summary field to total the number of refusals and a field to give a summary of the penalties (The penalties are contained in a calculation field based on the refusals). 

                                I have tried to put a summary field in the competitor field but t wont let me summarise data from another table. Could you be a little more detailed on how to do this.

                                Thanks for the One Row Portal Idea, its worked very well.

                                Im very new to this platform, thanks for your patience. 

                                • 13. Re: Replacing Values in a Portal

                                  If you read my previous posts again, you'll find that I said to define the summary field in the Jumps table and then put the summary field from Jumps on the competitor layout. It thus is defined in the table of the data it will summarize, but the relationship between competitors and Jumps will control what records are used to compute the sub total.

                                  If you want to define a sub-total field in the competitor table, you can use a calculation field with an aggregate function such as Sum that sums a field in the related table. This produces the same total as the summary field defined in jumps, but will bypass any portal filtering. On the other hand, it updates more smoothly than the summary field. (The main advantage to using the summary field is if you end up using portal filters. When you place a summary field from a related table in a portal to that related table, the summary value will reflect any effects produced by the portal filter.)