FileMaker 8+ gradient progress bar with one field - without containers

In the following article I will share a technique that I implemented in our current development, which needed FM8 techniques, as our client currently uses that.

I needed a progress bar, which looks great to show a script progress on a found set.

I didn't want to use containers and started thinking about how I would be able to create the progress bar without them.

Here's what I came up with:

The progress bar is a simple repeating field calculation(text) with 100 repetitions.

It uses the following calculation:

Let ( [r1=123;g1=80;b1=200;r2=10;g2=180;b2=50] ;

If ( Truncate ( (Get(RecordNumber)/Get(FoundCount)) * 100 ; 0) ≥ Get ( CalculationRepetitionNumber );

TextColor ( "█" ;RGB(

r1 + Floor((Get ( CalculationRepetitionNumber ) * (r2 - r1) / 100));

g1 + Floor((Get ( CalculationRepetitionNumber ) * (g2 - g1) / 100));

b1 + Floor((Get ( CalculationRepetitionNumber ) * (b2 - b1) / 100))

)

);""))

The first part of it is a LET calculation where I set up the beginning and the ending RGB colors, which will be used to calculate the actual repetition font color. The second part is a percentage calculator based on the current record and the found set.

The last part is used to calculate the actual RGB color.

The layout is simple, I just added the 100 repetitions, resized it, and set the font size to 72 points. I also added a fill color to the containers, that is what you can see under the gradient bar.

By modifying the rgb values you can come up with any color gradient you like:

I attached a sample file where you can find the calculation, investigate it, etc.

You can download it here.

Please check the demo file, and use this technique it if you like it.

Please visit our site, http://crm.fm which has our main FileMaker product, and support us by buying it. Thanks!

The material on this document is offered as is. There is no representation or warranty, expressed or implied, nor does any other contributor to this post. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

Moving in your previous FileMaker found sets

In the following article I will share a technique that I implemented in our CRM system quite long time ago. It's about getting back to your previous found sets.

The main part of most business softwares is the Search part, by which you can create complex search requests (extending existing lists, constraining, etc).

The main problem is that once you did a search and got a search result, you cannot get back to your previous list, or the one before that one, etc.

We faced this problem, and tried to implement a method by which you can move back and forward in your previous 5 (can be increased... see below) found sets.

FileMaker makes this quite easy, as a new table occurrence of the same table can hold a different found set!

So here is the technique. (Be sure to download the demo file I attached, so the you can understand this!)

You need to create five(or as many found sets you want to keep) table occurrence of your result table. In the example I attached It's the clients table.

So I created Clients_1, Clients_2, Clients_3, Clients_4, Clients_5 table occurrences in the relational graph and created 5 empty layouts with the same name (the source table of them was the appropriate table occurrence!).

Then I created two global fields, ( CurrentFoundSet_Global- This one is to know which found set we are currently on, and TotalFoundSet_Global- This one holds how many founds sets have been saved already).

The next step is the [SRTN]- SaveFoundSet script, which saves the current found set to the appropriate place, and checks if we have reached the highest value we can keep. If we did then it shifts the found sets left, so we loose the very first one.

This is done by the Go To Related Record script step on the appropriate layouts.

The [SRTN] - RestoreFoundSet script is to restore a found set.

There are two other scripts which are attached to the Restore Previous and Restore next buttons. (RestorePrevious,RestoreNext).

The saving happens at the search layout, in the [PANE]- Main bar script.

I attached a sample file where you can find these scripts, you can download it here.

Here is how you can try this:

1) Click on the Search Pane

2) Enter a "a" in the FirstName field, then press Enter

3) Go to the Search Pane again

4) Enter a "b" in the FirstName field, press enter

5) Go to the Seach Pane again

6) Enter a "c" in the FirstName, press enter

7) Now you can navigate backward in you found sets, and see the results.

This can be improved, modified, just wanted to share the basic idea of this thing. Please check the demo file, and use this technique it if you like it.

Note: In the sample file I used Brian Dunning's sample data file.

Note2: The original idea came from this article by Danny Dawson, worth reading!

Please visit our site, http://crm.fm which has our main FileMaker product, and support us by buying it. Thanks!

The material on this document is offered as is. There is no representation or warranty, expressed or implied, nor does any other contributor to this post. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

QuickTip: Creating a drop down list from an unstored calculation

The following technique is a quick tip, which may be known by some, but I needed it so many times in our current development that I thought I would share it.

If you have ever tried to create a value list from an unstored field, you may have noticed the following error message that filemaker gives you:

This means that our value list based on this calculation won't work.

What can we do now? Don't give up!

It's really simple.

If you have an ID field in your table (auto enter, serial), create a relation based on this field to itself like this:

(ID=ID)

The next thing to do is to modify the value list, so that it only includes values from this relation:

If you press OK now, you will still see the error message appear, but the value list will work now.

Please visit our site, http://crm.fm which has our main FileMaker product, and support us by buying it. Thanks!

The material on this document is offered as is. There is no representation or warranty, expressed or implied, nor does any other contributor to this post. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

Speeding up related record creation with importing

The following technique can be used when you have to create related records on a huge found set.

What is the main problem?

FileMaker can be really slow when you manually create many related records using a script, and we faced this when we developed our mass action (bulk mailing, exporting) part of our FileMaker based CRM. (Note: in our CRM there are two other related tables in which we needed related record creation which made things much worse)

Let's say you have client table, which has about 100.000 records. You also have a related table called History, which is to hold the events that happened to the clients.

If you try to add a history record to all clients in the found set, you'll have a script like this:

Freeze window

Go to Layout[Clients]

Go to Record/Request/Page[First]

Loop

Set Variable[$clientid;Clients::ClientID]

Go to Layout[History]

New record/request

Set Field[History::ClientID;$clientid]

Set Field[History::Event;"Client exported by user"]

Go to Layout[Clients]

Go to record/request/page [next, exit after last]

End Loop

It's an easy related record adder script which adds a "Client exported by user" text to the History table(it's related to the Clients table).

This would work well on small found sets. But when you try this on a big found set, you'll find yourself waiting for ages (and things get worse if we speak about a shared database and online usage).

How could it be sped up?

This is where exporting and importing can be a good solution, as you may noticed these functions create records much much faster.

This is how our script should be modified:

We'll need a global text field at the Clients table, where we set the "Client exported by user" text, let's call this Event_Global.

Now we can do an export on the clients table (we only need ClientID, and the Event_Global) to a temp directory in FP7 format.

After this we go to the History layout (and also table) and import that exported file as the following:

Clients::ClientID to History::ClientID

Clients::Event_Global to History::Event

And we're done.

So the modified script is the following:

Freeze Window

Set Field[Clients::Event_Global;"Client exported by user" ]

Set Variable [$path; Get(TemporaryPath) & "export.fp7"]

Export Records[No dialog; "$path"] //Note: here you need to add field Event_Global and ClientID

Go to Layout[History]

Import Records[No dialog;"$path";Add;Mac Roman]

Go to Layout[Original Layout]

And that's all.

So first we set the global field, then generate a path using Get(TemporaryPath) and save it to the $path variable.

Then we export the fields, go to the History layout and import it into the history fields.

Note: You need to be sure to set up both the export and import orders correctly!

Note2: Do not store sensitive data in temporary files, or if you have to make sure you delete it immediately after the script has finished!

Please visit our site, http://crm.fm which has our main FileMaker product, and support us by buying it. Thanks!

The material on this document is offered as is. There is no representation or warranty, expressed or implied, nor does any other contributor to this post. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.