Our company is considering returning to FM after a long hiatus and working with a custom Ruby on Rails web app (I posted here earlier but can't seem to reply without paying) and was hoping some developers here would be able to answer some generic questions as my FM development process is rusty.

Any tips or useful links would be SUPER appreciated:

- Can FM allow for what rails calls polymorphic relationships? For example, a files/attachments table that will house all attachments for different tables? If not, how can you go about allowing users to post multiple files to each record in FM (unless the basic answer is create sub document tables for each)?

- What is the optimal table structure in FM for best performance? Any major differences from typical SQL based DBs?

- Is the trend now to move design/layout, scripts, and data to different file? For performance or ease of development?

- Is it recommended to silo different business units (with shared access to common data) or combine into one FM file? Is this relatively straightforward to do?

- Any beautiful filemaker layout references?

- Any way to incorporate effects into layouts?

- Any references for complete audit trails in FM12? Is something like this built in?

- (edit) Can you easily include graphics for retina displays? (Does FM12 work well with existing retina on both mobile and new laptop?)

Again, any help would be very useful. We're starting to develop for more iPad/iPhone clients and FM12 was heavily recommended. Would love to hear if anyone had similar experience or any other thoughtful advice. Looking forward to joining this community again.

- Can FM allow for what rails calls polymorphic relationships? For example, a files/attachments table that will house all attachments for different tables?

sure:

tableA::ID_record = fileStore::ID_related_record

tableA::ID_table = fileStore::ID_related_table

tableB::ID_record = fileStore::ID_related_record

tableB::ID_table = fileStore::ID_related_table

In tableA, tableB, etc, the table ID is a fixed string, I use three letter abbreviations.

- What is the optimal table structure in FM for best performance? Any major differences from typical SQL based DBs?

How long is a piece of string?

Data size: The layouts are very important to filemaker web applications. Filemaker returns the entire record set, the fields in the record are defined by the layout. If they are not on the layout web does not see them. Make layouts fit the task.

Language: FMP is not based on SQL. However, 12 has SQL tools so the story is changing.

- Is the trend now to move design/layout, scripts, and data to different file? For performance or ease of development?

Both the single file camp and the Separation camp have powerful arguments in their favour.

Web apps don't care. They like to speak directly to the file storing the table.

- Is it recommended to silo different business units (with shared access to common data) or combine into one FM file? Is this relatively straightforward to do?

That decision can't be made without knowing the business very well. You are thinking of using the separation model and providing different UIs to different groups. Will that make it easier to manage or not?

- Any beautiful filemaker layout references?

Make it easy for people to get their work done and they'll forgive the fact that your layouts were drawn by an angry child with a box of crayons.

Any reason you can't use FMP ESS (External SQL Source) to connect to your current Web database, if its SQL? It should be pretty easy to either create FMP shadow tables to the current SQL table(s) and script the updates between them,

OR use the ESS directly in FMP.

It really depends on where you are going, why you are going there, and how you want to get there.

Checkout https://github.com/lardawge/rfm if you ever need to have Rails talk to FMP. The instructions say to use "username" in the Server setup but it is actually "account_name".

There are many ways to talk to API's but since I am a Rails dev myself, I write most of that in Ruby. I put the code in a field and set a global variable on startup. If you put the Ruby code in a field, there is no need to use the list function with all the quote escaping. I then call it using the Beezwax free plugin. http://www.beezwax.net/products/bbox

Polymorphic relationships: Sure. Just use UUIDs for your keys and you don't even need a second field to hold the table name.

Optimal table stucture: Depends on a lot of issues. FM is a still a relational database management system with its own quirks. It doesn't natively enfore referential integrity. There are far fewer data types. NULL is handled differently.

Separation model: You probably want to. It can make development slightly faster on large systems. Others will disagree.

Siloing: Are you separating business units mostly out of convenience or security requirements? You may want to consider a separation model with different interface files for each units.

---

---

---

200 concurrent users: FileMaker may not be the best choice for you. That's really pushing it.