ISO FileMaker Magazine: FileMaker Video Tutorials, Templates, Help & More

Very good episode, I learned a lot..you have a really clear way of explaining things (even things that are way beyond me), and you are very easy to listen to!

The idea of this series is a great one, especially for would-be developers like me who've graduated from Hypertalk, and are struggling with Scripting. The thing I really hope you'll get into (either in this series or a special one) is framing the relationship tables off the bat--despite having read everything I can lay my hands on, this always seems to get screwed up in my solutions, ending up with portals that won't work because I never got the basic relationships right in the first place. There seems to be a chasm between really simple databases and really complex ones, so I'm hoping this series especially will bridge the two..

Having said that, I've watched many of your more complex videos, and I always take a lot from them..I'm hoping at some point, suddenly I will 'get' it!
Cheers
Sean
FM9.0v3Advanced

Hey Matt,

I listened to your “Predevelopment” movie and perused your “My Invoicer” solution with great interest. What you’re doing and your plan to build a solution on an installment basis is a good one. I see so many solutions that have been pieced together with one-developer scripts, calcs and ideas here and other developer’s scripts, calcs and ideas there. Your 1. 2. 3. approach on the monthly bases will be very helpful to many fine feeling fellow FileMaker-Ins.

As a developer I like to think I know my way around FileMaker pretty well. But to not follow your solution building process would be to say, “I know it all, remember it all and couldn’t be taught another thing”! My wife would tell you that’s my natural state however…

The only thing I’d like you to change is that you build it as a “Separation Model”! I know from your movies it’s not your first choice, but with the advent of 8.5 and 9 it’s a whole new deal. Everyone understands the Single file pyridine, or they should. Think Separation Model Matt, there are 10 good reasons to build that way and not a single one not to. Yes, it takes a little more development time, but the advantages out weigh the slight difference in time of development big time!

OK Matt, bring it on as a “Separation Model”…

Harry

Hi Matt,

I have been a developer since 2.1 and like to think I know my way around FileMaker, but this new series is just what the doctor ordered. I learnt a great deal and can't wait to see the solution evolve.

Please be sure to address the issue of interface and how important it is to decide from the outset the size desired. Furthermore, a dynamic interface that utilizes the excellent art in your Scriptology would be a nice touch if you favour that approach.

The data separation model mentioned above is also indeed very important for an awesome solution.

Keep it up.

Best regards,

Ralf

I just downloaded the file and am delighted to note that it already includes a very scalable interface while seeming to make provision for the data from structure separation. It is easy to see that the end result will likely be very awesome indeed.

Whenever adding tables I have found it to be very helpful to include what I call my core 10 fields. They come in extremely handy when actually working with the data. They are:

Created By (Text - Auto-enter Creator Name, Prohibit Modification of value)
Created On (Date - Auto-enter Creation Date, Prohibit Modification of value)
Created At (Time - Auto-enter Creation Time, Prohibit Modification of value)
Modified By (Text - Auto-enter Creator Name, Prohibit Modification of value)
Modified On (Date - Auto-enter Creation Date, Prohibit Modification of value)
Modified At (Time - Auto-enter Creation Time, Prohibit Modification of value)
Count (Calculation - Unstored - Get(FoundCount)
Layout Name (Calculation - Unstored - Get (LayoutName)
Table Name (Calculation - Unstored - Get (LayoutTableName)
Record Number (Number - Auto Enter Serial - First number 1000 increment by 1, Prohibit Modification of value)

While its a pain to create the above in every table it is easy to create them once in a separate database and then import them along with a formatted layout into any solution.

I can't wait to see the next installment.

Best regards,

Ralf

Matt:

I think your attempt was "right on". I have only been developing in FM for 2 years. You brought up some excellant points that can only be addressed buy someone with experience, or shall we say the school of hard knocks. I learned quite a bit... and... your points will make the developing process easier, faster, and better for the client. Great job..stay on this track!!

Tim Veach

I've got about 7 years experience as a FileMaker Developer (That's 1 year experience repeated 7 times!) developing for my own use.
Every time I get into one of these forums or local discussion groups I find that even at 6' tall I'm too short. Some of these terms go right over my head.

What's a "Separation Model?"

Count me in for the Separation Model, though I sure appreciate any angle.

Now that portals are dynamically resizable, I've used the VisiblePortalRows CF ( www.briandunning.com/cf/684 ) to update your Portal Centering technique. I can think of another solution and maybe you have a clever update of your own that you could include?

Speaking of awesome, I would also still love to see some "Ultimate Multi-language" starter file (or best techniques thrown together in one or two vids) someday. This invoicer doesn't seem the place right now, but you never know :) Likewise, I still hope that one day there will be an "Ultimate Encryption" video, still easy enough to use in databases for passwords, invoices... ;-)

In Layout Setup > Show records from, I always seem to be using the similarly named base TO, which leads me to think I'm missing some typical cool uses of chosing other TO's there? Also feels related to calculations where you can chose context. I get what it can be used for, but I seldom have the need to change it, so maybe I'm also missing some great cliché applications for those?

Will try to come up with new suggestions as they arise.
Anyway, thanks in advance for all your efforts, Matt! :-)

Matt,

This is a great project and I am looking forwards to following along.

I agree with the other posts that request you to undertake the Separation Model of development. I think I understand the theory but I cannot see how we can have a full Separation Model in Filemaker. Please prove me wrong.

Rgds from Down-under

Martin Fathers
Melbourne
Australia

aHey Matt,

Long time follower .... First time poster.

The place I am contracting at now and rebuilding D.S. Files are pretty cool.
On the server side there are files.. Master Forms(Interface) and Master Data (Data)
On Field Tech's machines are file. Forms Data Collector (Interface) and Forms Data (Data)

Field Techs fill out forms on their machines and then connects to server and a posting script runs to transfer
data from their local machine to the server files. Anyway both sets of files the user opens the interface file and the data file opens hidden. All the layouts are in the interface and you display layouts using the Data files relationships. Most of the scripts are in the Interface file. Although some are needed in the data files.

One thing I strongly recommend is when you are designing these file, the list view should actually be a form view with a portal to the data file and you uses filters to show data you want to see. Using values lists from the fields you related to. For example In the data file you define a field called FilterPersons = calc = "All" & ¶ & Persons FullName. For the global field g_FilterPerson choose in the valuelist setup the filed in Data file, FilterPersons. When you pop the list it will show all or you can select the persons name. One the file has 14 type of the filters to quickly reduce the search in the portal. some of the value list are relational depending on what was chosen in previos field. I.E. If you choose North only the Northern Location show in the Location Filter and So one .... Another file called Compiance views forms and calculates due dates and lets you know if you are compliate to schedule of the forms. I hope I not boring you.

Bottom line is cafefully plan out the structure of a data sepation before it is too late. It can either be a great system or it can be a monster.

Robert Schaub
East Hampton, CT

Dear Matt,

Good to see the positive endorsement of the request for a separation model where the data is separated from the structure.

One further reason to pursue this approach is because in future many users will want to make their solutions accessible over the web. They can do this easily with Instant Web Publishing but that has considerable restrictions and costs. Alternatively they may want to have a regular html website of which just some sections interact with FileMaker data. For this group it would be ideal to have direct access to just the data since they build the interface with regular tools. A good example of such a tool for mac users is RapidWeaver from realmacsoftware. This provides a very powerful, flexible and yet easy to use web site creation system. Because of its plugin architecture there are lots of cool plugins available that really enhance the system's power. We even created such a plugin called Links whose purpose it is to bring access to FM data into a regular website (http://www.secure.cc/plugins/index.html).

The plugin is only beta and needs to be reworked and as such is not currently useable, but it certainly shows the way I think how web sites will be linked to preferably structured databases before too long.

Best regards,

Ralf

I love the project idea, I have learned alot from the first video and could imagine how much fun the following videos will be. I think it is a great idea, and I will support it 100%.

Matt
I am glad to see someone made a video that shows how to start designing a data base and how to get to the next levels. I am favor of the separation model as I have been using it for the last couple years.
It is a little harder to implement and you have to give it a little more thought but it keeps the users from access to all records where accidents can and do happen.
Keep up the good work.

Lionel

Matt could you mention during the "From Zero To Awesome" series, when mentioning the separation method, that there is a slight error with regards to aggregate calculations and the separation method.

Ever since FM7 I have had one problem with the separation method with FileMaker. The aggregate functions applied to a calc field doesn't update while performing record additions inside a portal. What I mean is if I have set the portal relation defined in the interface file, which allows the creation of records via the relation, it will not calculate any of the aggregate functions based off the portal records, until I exit the portal.

Example:
Invoice and Line Items, 2 table. I want to add records to the line items table via a portal which exist on a layout based on the invoice table. Each record added to the line item portal has a price field. I Have established a calc field based on the aggregate function 'Sum' which will add up all the prices of the records in the line item portal.

This all works fine until you separate the fields into a data file and display the data through a separate interface file. That sum field will not recalc as you add records to the portal until you exit the portal, such as clicking outside the portal.

I hope I have exampled the problem well enough for you to understand. I can always send a sample file too.

Any comments?

I'll be following this for sure. I'm actually doing a rather large (for me) student information system that I'm going to move from a single file to the separation model. This is great timing for me for sure!

Have at it!

Matt,
Thanks for looking into doing this solution as a multi-file system. I've been trying to do the last couple of my solutions this way. One potential 'gotcha', however, is that the current version of the FMPro PHP API does _not_ seem to be able to pull portal information from a separate file. That is, if your FileMaker object is built using a layout in file A and a portal on that layout is related to a table in file B you will not get any data through that portal. In fact, you will get errors.
I don't know if this is a bug or by design. But, interestingly, all of the FMI examples and the documentation always use portals with data from the same file.
Of course, this is not insurmountable. For my work-around I just created an additional FIleMaker object in file B using the key field from file A.

Anyway, this will probably not even crop up for your solution unless a future video covers hosting it on FileMaker Server (which would be cool, by the way :-)

Thanks for the great work. I'm looking forward to see some ways to help make my stuff more efficient and reliable.

Hey Dhrakar,

It's your naming convention. You need to check your Relationships names so the Relation names in your Data File, EXACTLY matches the relationships in your Interface File... It's the one deadly sin of the separation model, they must match!

There are no secrets here. It it doesn't work in SM it will not work in the Single File system either. There is no difference other than in one the data is seperate...

Harry

Thanks for the tip Harry. Please note, though, that the portal works just fine in the client. It is just in PHP that you cannot pull any data. During the Beta of the APIs, I had cases where this would and would not work from PHP -- now it just does not work.
The relationship that I'm using from file 'A' does not even exist in file 'B' (the gory details are that I have a file with computer usage data. One of the fields is the group ID. I use the group ID to look up information in a different database about the primary investigator, etc.) I'll poke around a bit a try something like setting the key names to the same.

Thanks.

Hi Matt

Ever since developing in fm 5 I have used fields to display a finer checkbox or radio button graphic, than the ones provided by Filemaker. (which is one of the most ugly graphics and they STILL haven't changed that)
Anyway, this approach is also used in other situations. My question is; If we use your approach in theese series (latest article: Predevelopment Data) and use Separation model. This includes using data file only for data and valuelists and the interface file for graphics.
I would like to show a graphic part if the value of Field1 (in the contact table) is 1.
As I see it, there are 2 ways that will work; I need to have a duplicate table in each interface file, which should be updated with as many records as in the data table Contacts. This is not a great way due to the heavy maintenance work.
The other way is to use Global variable $$Graphic[1] which works fine, but I will still need to load them from the datafile, as Global variables is file dependant. If I have a lot of graphic icons in my graphic table (which needs to be in the datafile in order to use them in a calculated field) it is a heavy startup procedure if the solution is used over the internet.

The ideal solution is that my graphics table is contained within the interface file and loaded into global variables at startup. I then have an Interface Table, holding session specific fields (and contain only 1 record). In this table there should be 1 field to display this checkbox on the Contacts record, but populated to all Contact records.
However, I have not been able to think of the calculation needed in this scenario. It should be something like: If( Contact::checkbox = 1 ; $$Graphic[1] ; $$Graphic[2])

You have walked around this topic many times, but never presented a solution. How can this be done ??

Hi Matt

Thanks for your answer.

You have previously made a video about how to store graphics into $$globals, which I use now. The great thing is that the graphics is loaded into the client machine's memory and therefore loads fast. I use this primarily to buttons and checkbox graphics. The display e.g. of the checkbox graphic is fast, when it is loaded into $$globals. However, If I have 40-50 graphic records in the graphic table, the startup script, which loads the graphic records into $$globals, takes quite much time. Especially when the data file is opened over the internet.

My schema is;
Data.fp7 holds all data and the graphics table. This file is served from Filemaker Server.
Interface.fp7 is the interface file (which holds no data and could be table empty) This file is placed locally on the client machine.

Beccause $$globals is file dependant, it is neccesary to keep the graphics table in the datafile, if I want to show the checkbox graphic in a field on the Customer record. If(Checkboxnumber = 1 ; $$Graphic[2] ; $$Graphic[1])

This forces me to run the startup script, which loads the graphics into globals, from the data file.

What I would like to be able to do, is to hold the graphic table in the interface file and load the graphics into $$globals locally on the client machine. This will reduce the "startup time" dramatically as the "heavy" graphics always stays local on the client machine.

However, in order to make this work, I need to have a table (lets call it DisplayGraphic) in the interface file with a field to display the checkbox icon.
I imagine that this table only have 1 record and in the relation diagram I have a TO with the basetable; Customer and a TO with DisplayGraphic. Thoose 2 TO's is related to each other with X relation. (then there is always a relation)
The big challenge here, is to have a calculation for this field, so that it will display the proper checkbox graphic icon, based on wether the Customer::Checkboxnumber is 1 or ""

I hope that I have clearified, what the goal is, that I am looking for.

…and you certainly have it.

I've been dabbling with FileMaker since the days of Forethought, before Claris and before Microsoft took PowerPoint from Forethought, buried it for two years and then announced they had discovered something new. (Is my bias showing?). At any rate, I've mainly sold FileMaker and only used it on occasion. When I made attempts to get serious other things came along. Now that I'm free from selling and supporting computers in a big way I'm getting into FileMaker more deeply…baggage and all. By baggage I mean old habits and messed up concepts. Your series is helping straighten out my head and I do appreciate it. Sorry to say it but the official FM materials come up a bit short and what you do here is a great and practical supplement. I'll pay my dues because you're deserving.

Now, as to that separation model thing; just be sure someone between novice and expert (like me) can follow along with the logic as well as the how to.

Keep up the great work. By the way, I do appreciate your eye for graphics.

I'm completely new to filemaker and databases and saw a few of your sample videos online. I decided to purchase 3 mo worth after reading the positive views. However, I now find myself completely lost! I started with the zero to awsome series and soon found myself uterly confused after the first video. The following videos in the series are difficult to find and order confusing. You seem to have started vearing off into random areas without keeping to your original premise of designing a complete database from scratch. At times you speed thru your mouse movements making it difficult to follow as well. Please help. Where do you suggest I start in order to get a grasp of this program? I like your set up and style, but I feel I am way in over my head! There does not seem to be a clear order in terms of watching your videos, am I missing something?

I am doing some website content creation work at the moment but I'm planing to start learning as many things as possible about file maker in the future. I think life is too short to spent it in meaningless ways and this is why I try to learn as much as possible as fast as possible. And this is why I want to thank you guys for.