1 2 Previous Next 21 Replies Latest reply on Mar 5, 2014 4:24 AM by fmpro_novice

    Help with a formula



      Help with a formula


           I have 4 records listed in a portal. And each of those records has a field called "Number." As an example, those "Numbers" are 4, 8, 2, 12 (but will be totally random).

           I need a formula that would put the lowest number of the four, which in this case is 2, and change it to 0.

           Then, take the four and change it to 2, the 8 and change it to 6, and the 12 and change it to 10.

           So the original "Number" which was the lowest of the four went from 2 to 0. So the other three numbers need to be reduced by 2 also. Whatever the lowest number happens to be, whatever takes that number to zero, then the other 3 numbers would be subtracted that number.

           Does this make sense to anyone besides me? And if so, what would such a formula look like?

        • 1. Re: Help with a formula

               A portal indicates that you have a relationship between the layout's table occurrence and the portal's table occurrence. What is that relationship?

               Is this a filtered portal?

               Do you want to change the values in this actual number field or use a second field to show the modified values, leaving the original values unchanged?

          • 2. Re: Help with a formula

                 I would actually like to leave the original field the way it was. Then, show the modification beside it.

                 The portal is a filtered portal.

            • 3. Re: Help with a formula

                   You didn't answer one question: What is the relationship defined for the portal?

                   And since the portal is filtered, what is the filter expression that you have defined for this portal?

                   I need that info to come up with a way of accessing the same set of records shown in the portal in a calculation such that you can get the results that you want here.

              • 4. Re: Help with a formula

                     I'm sorry, but I don't know how to answer either question.

                • 5. Re: Help with a formula

                       In Manage | Database | Relationships. You have Tutorial: What are Table Occurrences?, the "boxes" that each represent a table in your database. In Layout Setup | Show Records From, you have selected the name of one of these table occurrences. In Portal Setup | Show Related Records From, you have selected a different table occurrence. In Manage | Database | Relationships, there's a relationship linking the layout's table occurrence to the portal's table occurrence. Assuming that the layout's table occurrence links directly to the portal's, what pairs of fields have been specified to match a record in the layout's table to records in the portal's table? what operators, =, <, x.... have you selected for those pairs of match fields?

                       And in Portal Setup..., there's an option to select for a filtered portal. When you select the option for a filtered portal, it opens up a Specify Calculation dialog where you then specify the portal filter expression that will take the matching records from the relationship I just described and further "filters" the records by only allowing those records for which the portal filter calculation evaluates as True. Please copy and paste that calculation into your next post here in this forum thread.

                  • 6. Re: Help with a formula

                         The relationship is =.

                         The Specify in the Portals Setup is     not 0

                    • 7. Re: Help with a formula

                           I have already successfully created a summary calculation whereby the four numbers are added together. So, I'm at least that far.

                      • 8. Re: Help with a formula

                             That still does not answer my questions. I need the fields used in that portal filter expression. I need the match fields used in that relationship. What field in the layout's table matches to the portal's table? What field in the related records cannot be zero? I can guess that it's the same field that you want to use in this calculation, but would prefer not to guess...

                        • 9. Re: Help with a formula

                               The first field is called HNumber and the result from the calc is called HOverrider.

                               I'm afraid this still does not answer your questions. I'm sorry. I just don't get it, that's why I'm here asking for help.

                          • 10. Re: Help with a formula

                                 Then it would appear that you are trying to modify a database created by someone else. Your lack of knowledge about how FileMaker works makes this a bit dangerous. I hope you are working on a copy of the file so that you can always toss the copy you are changing and try again with a new copy if things go wrong for you.

                                 So you have this relationship:

                                 LayoutTable::HNumber = PortalTable::Hoverrider.

                                 If you told me the names of the table occurrences, I could be even more specific.

                                 I've asked you to copy and paste the filter expression directly into a post of this thread. Do that and we can take a stab at the solution.

                                 I'm asking for this information so that I can provide a very specific description of the solution needed in order to calculate the values that you want.

                            • 11. Re: Help with a formula

                                   I am working on a copy and I am working on a database that someone else created for me. I'm able to make modifications that work - when I see a calculation - then I understand it - and can make changes, and make it fit what I'm doing, etc.

                                   You asked me to go to Portal Setup, click Filter Portal Records, Specify. I did this, and the box said     not 0      That's all that was there.

                                   I know you are only asking for what will make this work and I appreciate everything you are trying to do. I think if you could just show me what a basic formula would look like, I could make it work from there.

                              • 12. Re: Help with a formula

                                     not 0 is always true. It will not have any effect on your portal--so I assumed that you were trying to say that a field was not zero but not naming the field in your post. Try removing that portal filter and see if you see any change in what appears in your portal. I predict that you will not see any change.


                                          I'm able to make modifications that work - when I see a calculation

                                     Yes, but if there was a portal filter in place that actually filtered, a simple calculation would not have worked, we'd have needed a more complex approach--possibly requiring a change to your relationships.

                                     Since we can ignore a filter that doesn't actually filter, you can add a new calculation field, let's call it MinVal, to the layout's table defined as:

                                     Min ( YourPortalTable::YourNumberFieldHere )

                                     for the example data you posted in your first post, this calculation should return 2.

                                     Then you can define a calculation field in the portal's table:

                                     YourNumberFieldHere - LayoutTable::MinVal

                                     You'll need to substitute your table and field names for those I've used in these examples.

                                • 13. Re: Help with a formula

                                       Thanks. I'll try this and see what happens.

                                  • 14. Re: Help with a formula

                                         I have this working for the first row of the portal, but the other rows show the same value as the first row. Not sure why. Any ideas?

                                         I have a similar situation where the HNumber is recalculated and that works perfectly for all the rows. I've just duplicated the process but replaced the calculator with MinVal.

                                    1 2 Previous Next