The following formula removes all '-', '(', '(', and ' ' characters from a phone number. E.g. it turns (123) 4567 8987 into 12345678987 This may be necessary if phone numbers have been imported with these unwanted characters. You can them use Field Format to display the phone number however you want. Translate(Translate(Translate(Translate(contacts.Phone, '-', ''), ')',
''), '(', ''), ' ', '')
Radio Buttons Field Label
Question: When using radio buttons, why can't I maintain the field label, as in getting it to appear above / below / etc. / anywhere in the box? Page 6-12 of my User's Guide shows an example with a field label Type of Customer. But I have been unable to generate a similar example. Answer: They got tricky. What they did to accomplish that is to create a Text Box and type the box label Type of Customer. Then they created their Radio Buttons box (outside of the text box, clicked the right mouse button, chose ARRANGE, BRING TO FRONT, and then dragged the radio buttons on top of the text box they way they wanted it. Play with it a bit and you'll get the results you're looking for.
Make empty fields disappear (... rather than just turn invisible)
It is possible on reports but not on forms. An alternative is to set up a multipage form and have a separate page for each field. On the first page, put a check box for each field set to automatically come on if there is an entry in the field. Then, when you are browsing through the forms, you look at the page if you need to.
Converting numbers to words 34 to thirty-four?
There isn't such a function built into any version of Approach. For Approach v96 there is a LotusScript called ConvertNumberToText in the Archive of Scripts (see section 3. Notices: How to get the Archive of Scripts) which works for numbers up to 999 999 999. If you are using v2.1 or 3.0* you need create another database with the numbers and related text versions of the numbers, and join it into your current database to the field that you want to appear as text. It might look something like this: number text
27 twenty seven
Sliding fields together on mailing labels
If you are having difficulty getting fields to slide together on mailing labels (such as first and seconds names), there are a couple of things you can do to improve the situation. 1) Use calculated type fields Set up a formula that combines the fields in the format you want in a calculated type field. Then put the calculated field on the label view instead of the individual fields. A "full name" could be set up this way, for example: FULL_NAME = Trim(Combine(SALUTATION,if(SALUTATION = '','',' '), FIRST_NAME,' ',Trim(MIDDLE_INI),if(MIDDLE_INI = '','',' '), LAST_NAME)) The above example formula only inserts spaces where they are required by testing to see if a field is "empty" and putting a "null" into the formula when it is empty. A "null" is two single-quotes with no space between them. For a complete label address with an optional second address line, the calculated field and formula would look something like this: CUSTOMER_LABEL = Trim(Combine(FIRST_NAME,' ',SURNAME,Chr(13),If(Isblank(ADDRESS2),Combine(ADDRESS, Chr(13),CITY,' ',STATE,' ',POSTCODE),Combine(ADDRESS,Chr(13),ADDRESS2,Chr(13),CITY,' ',STATE,' ',POSTCODE))))
2) Use a text object
Delete the database fields from the label and create a text object on the label view. Then insert the desired fields into it. In design mode, click inside the text object to put the cursor where you want a field to appear. Never type in the field name! Always use the "Text" option on the top menu bar, and the sub-option "Insert", then "Field Value" to add a field and avoid problems. You can add spaces between the fields and punctuation if needed, and put them on different lines. If a field is empty, Approach will not leave a blank line in its place. Database fields are expressed in the text object as <> where "database" is the name of the database and "field" is the name of the field. Calculated fields have only the field name in the brackets. For example:
There is some information the readme.wri file in the Approach directory about problems with international characters. To include a single quote or any other such difficult symbol in a text string it is sometimes easiest to use the Chr() function. For example to create the text string: 3'6" you would use: "3" & chr(39) & " 6" & chr(34). However, with single and double quotes you can achieve the same thing by simply repeating the quote: eg: "3'' 6""" (where the outermost set of double quotes are the boundaries of the string) but this can get a little confusing to read! You can find all the ASCII codes in old DOS manuals or a freeware utility like ASCIICAT at http://ourworld.compuserve.com/homepages/r_harvey
If all the phone numbers in your database have uniform formatting eg. (xxx) xxx-xxxx in the USA, then you can store the phone numbers a numeric field and set the field box to display them in the desired format. To do this go into Design mode, click on the numeric phone number field. When the InfoBox appears, click on the '#' tab, and select 'numeric' format type. Then click on 'Edit format' and enter (000) 000-0000 in the format code. If you are importing phone numbers from another application they may be stored as text fields containing both text and numbers eg. "(205) 765-4321". You can either import them into a text field, or use find and replace to remove all the unwanted characters, and then import them into a numeric field. If you have phone numbers in an Approach database in a text field, you can convert them to a new numeric field using Left, Right and Mid functions in a SET command in a looping macro: eg: to convert (205) 765-4321 from a text field (say 'TextPhoneNumber') to numeric format, set the numeric field to:
Middle(TextPhoneNumber, 2, 3) * 10000000
+ Middle(TextPhoneNumber, 7, 3) * 10000
+ Middle(TextPhoneNumber, 11, 4) (also see article 'Looping Macros' in this FAQ)
How to make a field invisible on a form
Set the field to invisible in a script using the following command: currentview.Body.fieldname.Visible=False You could use this in conjunction with an 'if' statement if you need it to be conditional.
Conditionally display a pushbutton 'The purpose of this script is to allow a pushbutton to be visible when data
'changes in certain fields. In my form the pushbutton is invisible when the
'form is switched to. When data in fields with script attached changes, the
'push button unhides. The pushbutton is clicked and both script and macro run.
'Open the script editor and choose Menu...New Sub (F3). Type, MakeInvisible,
'click, Make global sub and OK. In the Script Editor, finish this entry. Sub MakeInvisible (dis As Display)
dis.Visible = False
End Sub 'Repeat the process for the next global sub, MakeVisible Sub MakeVisible(dis As Display)
End Sub 'These two are your global subs you will call from another script.
'Next, on any form you have that has a pushbutton, go to design, properties,
'click on the button and note its object name. Return to the script editor and
'using the object dropdown box, toggle to the correct form, then object. Use
'the Script dropdown box and choose click. The complete the following Sub Click(Source As Button, X As Long, Y As Long, Flags As Long)
Dim dis As Display
End Sub 'All thats left is choosing which field(s) you want to monitor. Again in the
'object dropdown, find the object(typically your fieldname), choose change and
'complete the following. Do this for as many fields as you want to cover. Sub Change(Source As Fieldbox)
Dim dis As Display
End Sub 'The lines with Source.ObjButton should read Source.YourButtonName where
'YourButtonName is the objectname you noted earlier for your pushbutton.
'What happens? You are passing the argument dis to the sub MakeVisible or
'MakeInvisible and the sub processes the request. Try substituting a fieldbox
'object or any other display class object instaed of the pushbutton and let me
'know how it works.
'Written by Jerry Sikes , 1996
'(C) Copyright 1996 by Jerry Sikes
'Permission is granted to freely copy this script in electronic form,
'or to print for personal use. It may be use in any Approach database,
'but may not be distributed for profit either by itself or as part of
'a collection or database.
'Disclaimer: This script is provided as is without any express or
'implied warranties. The author assumes no responsibility for errors or
'omissions, or for damages resulting from the use of the information contained
'herein, or your own version containing your desires for the usage of the
Stopping Approach from using Marlett as the default font
Marlett font may be useful for something but its dammed annoying as a default font, so why does Approach sometimes insist that it is the only font for me?!
Well, there are a number of possible causes for this problem: * your default printer is set to "Generic/Text"
* your default printer is set to a fax, .pdf or some other software driver rather than to an actual printer
* you have a font named "Monotype Sorts" installed on your computer. Remove it!!
* you have a field or default font set to a font or style that is not installed on your computer. This is a particular pain if you accidentally uninstall a font which you have used throughout your .apr's. I speak from personal experience on this one. Months later I was still opening not-often-used .apr's only to find everything was now written in Marlett which is not a language I can readily read!
Deleting the body in summary reports
Use your mouse to drag the bottom line of the body frame up above the top line of the body frame. That way the body disappears and only the summary lines show up on the report.
Tips on layout of Reports / Altering report width
The default method of working with a columnar, repeating panel, or summary only type report is to use PowerClick reporting (see Approach Help for more information about it). In a nutshell, this feature works when you are in Design mode and have the View menu option "Show Data" turned on. Columns then become moveable and resizeable with the mouse, but working with individual elements such as the column header can be difficult. If you can't get your layout the way you want it using PowerClick, try turning 'Show Data' off (by clicking on it in the View menu). You can now resize and reposition the field boxes and column headings as required. It is not possible to alternate the background color in the line items of a report (like in a repeating panel). Therefore if you really want to do this you either need to export the data to a program that is capable of it, or create a really big repeating panel! Remember that a repeating panel has a maximum of 30 rows. A common problem with reports is that a second unwanted page is created to the right of the page you are working on. This is because one or more of your report objects (fields or text blocks) is extending beyond the right margin of your page. To find the offending object(s), turn off the "Show Data" option. Then under Edit, Select All to highlight all the object boundaries. Scan down the right margin and move/resize the object as needed. Remember that the object may be in the header or footer.
There is no automatic function to do this, so basically you have to get your Report to work it out itself. The following two methods have been submitted: Method 1: using imbedded IF statements:
-------- Create a calculated field with the formula: if(SCount(Surname) <= 25, 1,if(SCount(Surname) >25 and SCount(Surname)
<= 50,2, If (SCount(Surname)> 50 and SCount(Surname) <= 75,3,4))) This presumed that the surname field would be on every record and
that their would be a maximum of 25 records to the page and that I would
never have any more than 4 pages worth. You will need to adapt it to your requirements. Then placed the normal page counter field with this field next
to it. Eg: Page 1 of 4 Note that this formula will only give the correct result for a particular printer type and page layout. If either of these change you will need to check the formula. Method 2: using 3 calculated fields
-------- Create three calculated fields PCOUNT1, PCOUNT2 and PCOUNT3 using the following formula: PCOUNT1: (SCount(CONTENTS) + 4) / 90 PCOUNT2: Trunc(PCOUNT1, 0) PCOUNT3:
If(PCOUNT1 > PCOUNT2, PCOUNT2 + 1, PCOUNT2) Then put the following in a text block in the header or footer of
your report: Page <<#>> of <
Form letters (and multi-page reports)
The bad news is Approach (up to v97 at least) only supports single page form letters. If this is a problem then do your form letters in WordPRO, or some other word processor, and either have the word processor access the .dbf directly, or export your data from Approach in whatever format your word processor wants. This can be automated by creating a macro which exports the desired fields into a temporary database, and then opens your WordPRO form letter containing the desired embedded merge fields. You can complete the automation by imbedding a Script in the letter which performs the mail merge and perhaps starts it printing. This script would need to automatically execute when the letter is opened. A work around in Approach is to create your form letter as a Report. Eg. have the heading info in the header panel, the greeting
and opening paragraph in a leading summary panel. Body panels could contain a list of items to be billed, and a trailing summary panel could contain the total and include the "Yours truly," line and space for a signature. If you are keen the opening paragraph could be a calculated field combining various sentences based on flags in the customer record. When using this method for multi-page letters/reports, make sure you do not have a footer panel. Set the properties for both the memo field and the body panel to "expand" using the InfoBox (click on field and press ALT-ENTER). As long as there are no fields beneath the memo field in the panel, the memo field and the panel will expand to span as many pages as is required. Note: There is a bug in v3.02: A memo field that spans more than one page, can cause weird problems apparently.