Try the PatternCount () function.
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.
Thanks mr_vodka, can't believe how easy that was once I knew the function I was looking for, ta!
I found if I have alot of variation for a calculation I use a case statement.
PatternCount ( SpaceField ; "CarPark" ) ≥ 1; Area*Rate*52;
PatternCount ( SpaceField ; "Office" ) ≥ 1; Area*Rate;
PatternCount ( SpaceField ; "Equip" ) ≥ 1; Area*Rate / 52;
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.
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 :)
I second what Phil has already stated.
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".
Cool approach, I'll steal that one for sure, thanks for sharing.
Don't forget step #0...make a backup first.
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.