10 Replies Latest reply on Nov 3, 2009 1:13 PM by philmodjunk

    IF Function Difficulty

    jellis_nz

      Title

      IF Function Difficulty

      Post

      Hi,

      I'm trying to create an IF Function in a field where the calculation to be used is determined by whether or not another field contains a particular word, rather than the field actually being equal to a defined word.  Maybe an example will better explain my problem...

       

      Space       Area         Rate         Rental

      Office       500sqm     $50/sqm    $25,000

      Carparks   30cpks      $30/wk      $46,800

       

      The calculation field I'm refering to is in the Rental field which is calculated by multiplying the Area field by the Rate field.  However, if the calculation is for carparks then it needs to be Area x Rate x 52weeks.  This is where I'm trying to use the IF function... here the real difficulty arises in the large dataset I'm working with because in a lot of cases the field has a large degree of variation such as 'Carparks', 'Carpark', 'Carparks - Covered', 'Carparks - Open' making it difficult to simply make an IF function to return an answer if the Space field equals 'Carparks'.

       

      What I need is a way to make the Rental field formula something like this: IF(Space Contains "Carpark", Area*Rate*52, Area*Rate). However for the life of me I cannot find a way to devise a formula that returns a TRUE result if a field contains (or is Like) a string of letters.  Can anyone help me please?!

       

      I'm currently trialing Filemaker Pro 10 on Windows XP OS, so I'm quite new to the Filemaker software.

       

      Thanks!

        • 1. Re: IF Function Difficulty
          mrvodka
            

          Try the PatternCount () function.

           

          • 2. Re: IF Function Difficulty
            philmodjunk
               Ultimately, you might want to add a "units" field that you can use to determine which calculation applies rather than a text field that holds such a variety of hand entered data. Consider what happens if one of your fields misspells the word "carpark" as something that doesn't match your pattern count function.
            • 3. Re: IF Function Difficulty
              jellis_nz
                 Thanks mr_vodka, can't believe how easy that was once I knew the function I was looking for, ta!
              • 4. Re: IF Function Difficulty
                SmallTown
                  

                I found if I have alot of variation for a calculation I use a case statement.

                example:

                Case (
                PatternCount ( SpaceField ; "CarPark" )  ≥  1; Area*Rate*52;
                PatternCount ( SpaceField ; "Office" )  ≥  1; Area*Rate;
                PatternCount ( SpaceField ; "Equip" )  ≥  1; Area*Rate / 52;
                0)

                • 5. Re: IF Function Difficulty
                  philmodjunk
                    

                  Which can help, but doesn't completely solve the issue.

                   

                  What if some one enters "carpar" instead of "CarPark"? Using an IF or case function here is a short term fix. The long term fix is to re-design the database so that a field--such as the units field I suggested--whose input is controlled from a value list, is used to determine the appropriate calculation.

                  • 6. Re: IF Function Difficulty
                    jellis_nz
                      

                    I thought about setting up something like that - where the type of carpark must be selected from a drop down list in order to prevent any errors, however the dataset is so huge that it would take an entire week for me to standardize all the existing records!

                     

                    Instead of using the word 'Carpark' I've used 'Car' in order to minimise the chances of any typo slipping through the gaps.  I may have to add a drop down in the future anyway, just so the forms and reports are all uniform.

                     

                    Thanks for all your help team :)

                    • 7. Re: IF Function Difficulty
                      mrvodka
                         I second what Phil has already stated.
                      • 8. Re: IF Function Difficulty
                        philmodjunk
                          

                        Here's a method that might make the transistion to a value list controlled field less troublesome.

                         

                        Keep your current data fields unchanged.

                        Add a new text field and format it with a value list that restricts user options to just the values you want (Such as "Carpark").

                        Open the field definition and copy your If function that you already have working for you to the clip board.

                        Place this new field on a layout based on the appropriate table

                        Show All Records

                        Put the cursor in this new field and select Replace Field Contents from the Records menu

                        Select the Calculated Result option and paste your if function into it.

                        Edit the if function to return a matching value from your new value list instead of peforming the cost calculation.

                        Perform the replace

                         

                        Now do a find for all records where the new field is still empty. (Enter find mode and put a lone = symbol in the new field.).

                        Hopefully, this will be a small number of records where a type kept the calculation from assigning a unit.

                        Edit these records to fix typos and to select an appropriate value in the new field.

                        Make your new field a "require field".

                         

                        Done

                         

                        • 9. Re: IF Function Difficulty
                          ninja
                            

                          Cool approach, I'll steal that one for sure, thanks for sharing.

                           

                          Don't forget step #0...make a backup first.

                          • 10. Re: IF Function Difficulty
                            philmodjunk
                              

                            Frequent backups are always a good idea.

                             

                            However, since this replace is performed on a new empty field, the chance of creating a seriously messed up database through improper use of Replace Field Contents is a bit less.