A READER ASKS: How to reset FileMaker calculation field?

© 2010 Dwayne Wright - dwaynewright.com
From Dwayne Wright PMP - Certified FileMaker 10, 9 & 8 Developer
EMAIL: info@dwaynewright.com     TWITTER: dwaynewright

A READER ASKS
Wondering if you might have a quick answer to my question

I have about 4000+ records that I have monthly forecast fields that calculate a result using entries of two different fields (quantity- & unit price field).

In order to make the overview less crowded I just decided to add "/1000" to the end of the existing formula of each field (each month). I thought that this was an easy fix to eliminate long $ numbers in my annual layout but had to realize that none of the calculated $ numbers show up unless I go and retype at least the quantity field used to calculate the $ field.

Your suggestion on where to find info or how to solve this problem would be greatly appreciated. Thank YOU

-------
DWAYNE RESPONDS
It sounds like you have an “auto enter by calculated value” field setup and not a calculation field. A calculation field would automatically update where the former would not. There is an easy solution to the problem, however there isn’t any “undo” available if things go awry.

1) Backup the database, just in case there is a mistake made in the process.

2) Insert the cursor into one of the fields that is used in the calculation

3) Use the “Replace” command under the Records menu.

This command is used to replace the contents of a field in all records of the current found set. You can exchange the fields contents with a data string, an incremental serial number string or a calculated result.

4) You will want to replace the field with a calculated result. The calculated value is simply the same field. Essentially, you are replacing the contents with the contents that are already there. For example, I have a field called Blog. I will replace the contents of “Blog” with the calculated results of the field Blog. This will trigger the calculation to reevaluate itself and give you the desired results.

Again, I cannot stress enough that you must perform this on a backup of the file because there is NO UNDO option for the Replace command.

Here is a link to another post that might be of interest in regards to this topic...
Using The Replace Command

A READER ASKS: Keyword Search Integration

© 2010 Dwayne Wright - dwaynewright.com
From Dwayne Wright PMP - Certified FileMaker 10, 9 & 8 Developer
EMAIL: info@dwaynewright.com     TWITTER: dwaynewright

A READER ASKS
We need an effective way to apply categories and keywords to our records. One strategy we have used in the past includes entering several keywords from a small preselected list, into a single field separated by commas. These are searchable, but due to the restricted nature of the list, often don’t adequately describe the document. Another strategy has been to create multiple lists of categorized checkboxes. This seemed to work well for a small database restricted to similar documents used for a single project, although one had to click through several tabs of checkboxes in order to search for specific keywords.  

Our question is, have you seen any keywording strategies implemented that seemed to be particularly effective, especially for a database of rather diverse records?  

-------
DWAYNE RESPONDS
There is a way to blend the two different strategies into one workflow. Say we have both strategies employed. There is a field for free form keyword search entry and then keyword checkbox fields at particular areas of data entry. These keyword boxes would help setup a consistent keyword list for the records. Under the hood, I could use a calculation field that concatenates the data from the free form area and all the checkboxes. We can create a keyword search box, that uses the "under the hood" concatenated calculation. So the user would only employ a search from one area but it would integrate results from all keyword entry areas.

If we wanted to elaborate this design further, we could even integrate an "advanced" keyword search tab. This would allow you to select or deselect which keywords checkbox fields you might not want to use in a particular search, plus other advanced search options.

Scripted Omit Constrain Within A Found Set Via A Value List

© 2010 Dwayne Wright - dwaynewright.com
From Dwayne Wright PMP - Certified FileMaker 10, 9 & 8 Developer
EMAIL: info@dwaynewright.com     TWITTER: dwaynewright

The following situation came up in a recent “One On One Virtual FileMaker Training” session and I thought I’d share it with you. The resolution covers some areas that are not complex but seldom discussed or documented. For more information about our one on one sessions, check out our web site at www.dwaynewright.com.

A READER ASKS
I have been reading your blogs and want to thank you for all the great info you have shared. I have FileMaker 10 and I have a list of suppliers that I use for a report. The report is listed with supplier groupings by using a sub-summary part. The list is used by sales people to give them a good idea of the calls they need to make for the current month.

I've been challenged to find a way for a user to pick a name from a value list and have the corresponding entries omitted from the found set. The users will want to do this many times in succession, until they get the final listing just right. Writing the script to do this seems to be out of my league.

I tried using a Find script with Omit but I'm already in a limited found set. Doing that brings me to a totally different found set of records. I tried to use a scripted constrain find (from reading about constrain on your blogs) but I cannot find a way to script the omit for the constrain dynamically. It only seems to work if you "save" the last find (via the Restore option). That hard codes the Winery omit setting from the last search.

DWAYNE ANSWERS
This does present problems but the answer really isn't that far off from what you tried. The one thing that we need to add is a script variable and then to manually edit the saved find setting in the Constrain Found Set step. Take a look at the image below ...


What we do is set a variable equal to the setting in the global field (as set via a value list). Then we do the normal find script steps with the Constrain Found Set [Restore Option] step. We then open up the options for Constrain Found Set and manually edit what it is looking to search. We replace the hard coded Winery name with our script variable and we are good to go. 

So here you can see the lineup of dialog boxes that we have to edit what the Constrain Find is looking to return. You can see that we have edit that with the script variable we set earlier in the script.

A READER ASKS: Sorting By Language Value List

© 2010 Dwayne Wright - dwaynewright.com
From Dwayne Wright PMP - Certified FileMaker 10, 9 & 8 Developer
EMAIL: info@dwaynewright.com     TWITTER: dwaynewright

APPENDIX 1: A Reader Asks

A READER ASKS
I need a language value list, but there isn't one in FM 10 creation fields "options". I create a value list exactly in this form and I need to sort the field "thema" in this form.
numerical and alphabetical order of course not works

a
b
g
T
k
t
r
s
u
S

-------
DWAYNE RESPONDS
There is an option to sort a field by an accompanying value list. For the huge majority of the time, you will be sorting fields in ascending and descending orders. However the ability to sort via a value list can add some very interesting opportunities to your sort routines.

A sort by value list can be very helpful in the crafting of reports that might have grouped totals based upon a sub summary part. To sort a field by a value list, you click the field you want in the sort order. Next you click the radio button for Custom Order Based On Value List. From the pull down menu from that selections right, choose the value list you want to use.

A READER ASKS: Disappearing Records In List View

© 2010 Dwayne Wright - dwaynewright.com
From Dwayne Wright PMP - Certified FileMaker 10, 9 & 8 Developer
EMAIL: info@dwaynewright.com     TWITTER: dwaynewright

A READER ASKS
We keep an auxiliary DB for wine/winery info and reviews for our different products. Recently we've started having sporadic problems with items in the DB that do not show up once a script is initiated to sort the data. It seems to be the problem seems to be expanding to more than one item and I'm wondering if there is a way to rebuild a FM DB. Any ideas for me?

-------
DWAYNE RESPONDS
It sounds like you are seeing the “feature” of FileMaker 10 called persistent sort order. What happens is that if you sort a list and then add a new record, that record is sorted along with the rest as soon as you are finished with it. This means the record can immediately be placed somewhere in the listed entries where you cannot see it. It appears to have disappeared but that isn’t the case.

If this isn’t the situation for you, then we should take a look at the file and see what we can do about troubleshooting / repairing it for you.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2010 - Dwayne Wright - dwaynewright.com