Welcome to the MacNN Forums.

Gavin · Nov 30, 2007, 03:18 PM

Hi,

I'm starting a project that uses FMP as a front end for MySQL. I haven't used FMP for years and have a few questions.

Does anybody know a good message board for FMP?

This is for fairly tricky stuff like populating a pop up list from a related table over ODBC, exporting a found set as a uniquely formatted text file, vcard drag and drop populates several related tables, etc.

Thanks

larkost · Nov 30, 2007, 04:15 PM

The place you are probably looking for is Main Index - FileMaker Forums - Worldwide FileMaker Community - Powered by FusionBB, but there is also another message board from FileMaker that you can pay to be a member of (I am not, so can't evaluate it).

But to your list:

Value Lists is the answer to your first question. I don't use any external SQL tables at this point, so there might be a need to manage a local cache of the database in a replicated table (that is going to get messy if needed). Are you sure that using FileMaker and MySQL is necessary?

For the export question there is a good chance you are going to have to write up a calculated field for that and use a recursive custom function (passing a counter between the recursions along with the data... messy).

And without plugins you are not going to get the vCard part. I would do it with an AppleScript if it could be Mac-only. Possibly triggered by an internal script.

Gavin · Nov 30, 2007, 06:34 PM

Thanks, good info.

We're looking at a complicated set up. Multiple physical locations, web based interactions for clients and remote staff, FMP front end for things in the office, etc. So they need something beefy on the back end and a client that can interact with external 3rd party software in a way that a web browser can't. They already have FMP producing reports and flyers.

So far it looks doable, there are some things that are fairly easy in php but will need some creativity to do in FMP.

I'm actually surprised at how seamless the ODBC connectivity is. My related table data is popping right up in a portal.

I've tried the value lists but my problem is that FMP wants to use just a value rather than a label and value. For example an HTML from will let you have a label of Red with a value of 16; 16 is what I want in the DB. FMP tries to insert Red. I sort of have a work around in one case using a button with a script.

larkost · Dec 1, 2007, 03:04 PM

When you create the value list you need to make sure that the first column is the 16, and the second the label, and you only show the second column. Then you need to make sure that you use a pop-up to select the value. Occasionally you will see the "bad" value (usually when no label value is available), but this is a good solution.

Gavin · Dec 1, 2007, 07:53 PM

Got it! Thanks.

It's not letting me use the ODBC tables as a source (grayed out) so I'm going to have to duplicate all my value lists to the local FMP db and then somehow keep it in synch with the MySQL values, but generally it works.

Cool

larkost · Dec 2, 2007, 10:22 PM

Ya, I was worried about that. Depending on how up-to-date you need the data, you could make a cached copy of the data and then wipe-and-reload using a on-open script. If this runs too often, then you can create a preferences table, and put the date of last-run and then decide whether to run or not at each call.

Gavin · Dec 7, 2007, 11:09 PM

I think that's the plan. It turns out you can relate the second value - so all you need in the local table is a number. Essentially a table with one field - a simple record ID. All I have to do is make sure the number of records match. These will not change very much, things like: "Left, Right, Center" and "On, Off."