Browsed by
Category: Layouts

Searching Large Amounts of Records with Dynamic Portals By Name or Category

Searching Large Amounts of Records with Dynamic Portals By Name or Category

If you have ever wanted to search a large amount of records either by Name or Category, or other fields, there is a somewhat simple solution which uses an auto-calculated text field, which is used as a foreign key multi-value list and global fields to perform a relationship match.

You only need 1 table to perform this type of operation.

1. Define the fields

Let us say we have a table of companies which has the following fields:

id
name
category

To this, we add a fourth field which is set to auto-enter a calculation as follows:

"_all" &
If( not IsEmpty( category ) ; ¶ & "_cat_" & category ) &
If( not IsEmpty( name ) ; ¶ &
Left( name ; 1 ) & ¶ &
Left( name ; 2 ) & ¶ &
Left( name ; 3 ) & ¶ &
Left( name ; 4 ) & ¶ &
Left( name ; 5 )
)

What this gives us as a result when the name is “ABC Co.” and the category is “Phone Companies” is this:

_all
_cat_Phone Companies
A
AB
ABC
ABC
ABC C

In other words, we use the keyword “_all” with an underscore before it to avoid confusing it with any company beginning with “all”. That way, if we want to match any company, we would match against “_all” as the keyword.

The same idea applies with “_cat”.

And then, we enter up to the first 5 characters of any name so that we can search for up to 5 characters.

This field needs to be indexed with minimal indexing for it to work in our relationship we will create.

Next, we create some globals:

SEARCHOPTION
SEARCHCAT
SEARCHNAME
SEARCHFILTER

We use capitals to make them stand out as globals. Be sure to set them as globals. Naming them with capitals is not enough. We are following the standards as provided at FileMaker Coding Standards.

We then add an auto-enter calculation to the SEARCHFILTER field as follows:

Case( 

SEARCHOPTION = "all" ; "_all" ; 

SEARCHOPTION = "category" ; "_cat_" & SEARCHCAT ; 

SEARCHOPTION = "name" ; SEARCHNAME ;

 ""

 )

NOTE: IN BOTH AUTO-ENTER CALCULATIONS, MAKE SURE YOU UNCHECK DO NOT REPLACE EXISTING VALUE OF FIELD (IF ANY)

2. The next step is to set up your relationships and layouts.

Take a look at this sample file to see the rest.

Dynamic Portals Sample File.fmp12

Conditional Formatting for Highlighting Required Fields

Conditional Formatting for Highlighting Required Fields

Every now and then, you will need to highlight fields required on a layout/database. You may have observed that validation turned on in a table field can be cumbersome especially if that field won’t be on certain layouts. Here is a lesson to show a workaround for this, which also highlights mandatory fields which have been left blank by the user.

Here is a link to the file referenced in the above video:

Conditional-Formatting.fmp12.zip

How to Make Progress Bars in FileMaker Pro

How to Make Progress Bars in FileMaker Pro

Have you ever wanted to created a live-updating progress bar? Piecharts and graphs are a cinch, but the ability to make a good-looking progress bar seems almost impossible–unless you know how to use the Web Viewer Tool!

Using HTML/CSS and a few calculations, you can simulate a progress bar that will update in real time using Script triggers and the ExecuteSQL function.

Watch the video at FMTutorials on Youtube.

Creating Modal Dialogs

Creating Modal Dialogs

This tutorial is a remake of an earlier one, with up-to-date information on how to create a modal dialog using FileMaker Pro version 14. This technique is more simple, with FileMaker’s update to the New Window script step, which now allows for 3 different types of windows. You now have the ability to turn off resizing, close button and other features to make a truly modal window which cannot be exited or canceled by the user without following through with the desired action.

Autosizing in FileMaker

Autosizing in FileMaker

Learn how to use the autosizing feature in FileMaker Pro 14 to create fields and objects that resize themselves to fit the window width, height, or even to right align objects on the layout.

A very useful feature if you want to maximize screen usage and provide layouts that dynamically “adjust” themselves.

Leave comments and questions below.