How does FileMaker differ from Oracle?

Lounge (Discussions)
1

"coming from Oracle" ............

OK, here is a neat little FileMaker feature NOT found in Oracle :slight_smile:

Bit of background: FMs relationship diagram is NOT an ER diagram; it is much more. Tables are not directly represented therein, but are aliases to a table, called Table Occurrences.

You can build relationships between table occurrences that are, in effect, a query. (your Oracle stored procedure facsimile). That is a huge power multiplier.

1 Like
2

Indeed, although a limitation of the way FileMaker implements this is that certain parts of a query can't easily be built into the Table Occurrence.

Things you can do in a TO:

  • match fields (e.g. SELECT WHERE TableA.X = TableB.Y ) - including equal, less-than, greater-than, and Cartesian. Multiple fields
  • sorting (SORT BY)

Things you can't [easily] do in a TO:

  • constant matches (e.g. WHERE Table2.X = "completed"). [You can do this, but need to add a constant field to Table1 to use in the match.]
  • SELECT LIMIT N. [ You can sort-of accomplish this by using a filtered portal. ]
  • SELECT...GROUP BY. [ You can handle this by adding Summary fields in some cases]

I would love it if FileMaker allowed more flexibility, so a TO could really function like a View, exposing more of these standard database features.

2 Likes
3

I would love to see these features in the relationship graph.

Constants: Inserting a constant into the relationship makes more sense than building non-data into the data tables.

Select Limit N: The filemaker way is to create a portal containing N rows. Importantly, DO NOT ALLOW scrollbars. This is completely under-documented and WTF!

Select, Group by: Portal sorting can get you part way there but requires access to the entire data set, so it's a performance burden.

1 Like
4

At least this one is somewhat supported in the portal setup dialog. You can specify rows SHOWN but not the FOUND SET qty (although this is trivial code to do). Scrollbars off is an existing option.

As far as the constant in a parent table, that is a minor inconvenience, and, unlike portal filtering, where the entire dataset needs to load before the filter is applied (and FoundCount does not work), the relationship filter reduces the found set before populating the portal.

What I would REALLY like is a list view OBJECT, with all the capabilities of list view, but placed anywhere on a layout. Not sure there would be a need for portals at all, if that was available.