ESpringer

Thanks, both, again. To be clear, poking around within a single web viewer session has no appeal in this situation, because that single web viewer window would have virtually none of the power of filemaker that my existing solution is intended to harness, and also none of the conveniences of a dedicated web browser. So it's API or bust. The learning curve looks like the kind of work that would pay off at scale, but a bit steep for someone for whom FM has been not a career but something between a workflow convenience and a creative-procrastination hobby. (And of course these direct API queries will need to clear authentication too, and if I run into headaches there, I'm likely to be always unsure whether the problem is my own incompetence.) At least, as Wim points out, if I do invest time in setting up tools to pull data into filemaker using moodle's API, the resulting solution will not be so "off-label" as to be perennially precarious. -ESpringer

Wim and Comment, thanks for the supportive comments... I'm afraid design consolidation can't help; the problem is actually not just with record/layout/mode changes; it's that my SSO authentication just doesn't *carry* at all from any instance of a web viewer to any other instance. For example: On my wide screen, one layout has 2 web viewers side-by-side, for the first and second page of a history overview (that moodle stubbornly paginates, tho 2 pages almost always suffices). Now, when I authenticate through the first web viewer, the url&"&page=1" variant -- on the very same record and layout — remains locked out. (This wasn't true in earlier versions of moodle; I'd authenticate once and then everything would behave, and I could then scroll through student records pre-populated with calculated urls tuned to the relevant API variables.) I'm heartened that you're confident that learning about API to retrieve data directly is not too steep a learning curve... but I do have a day job . Also I hate to reinvent the wheel in terms of building that data back into friendly shapes. (Am I right that a direct API query would get me things like id-number-date arrays, but I'd have to put work into making them visually digestible?) For example: moodle's web interface already does serve up perfectly adequate graphs for each student's participation in activity X over time. This visual display over the web (once you're at the url) is a helpfully pre-invented wheel; it's the navigation that's the devil. On an ordinary browser, one has to navigate to the participants list, point-and-click to select a student, and then drill down among available reports and logs for a given student, squinting at drop-down menus to filter the desired info, etc., and then back out and do it all. over. again. for the next student. Once I had parsed the url structure of this or that "perfect report" for my purposes, FM's web viewers used to let me scroll through student records, glancing at each graph, already tuned to the variables I care about. While learning direct API queries could give me much more power in some ways (once I get over the intimidation), it seems that certain "it just works" experiences are a thing of the past because of this tightened security around web viewer "widgets"... is that right? (Is there any imaginable workaround?) -ESpringer

Wim, your reply is so helpfully informative. A bit devastating though. I've poured so many hours, over many years, into developing these solutions to get around the fact that the browser-interface for the CMS requires too many point-and-click navigation steps to achieve what a simple scroll through records or shift of tabs should (and did) do with FM's help (using calc fields to swap in various bits of the relevant urls). The idea of using moodle's API to interact with data sounds like it would require me to learn a whole new domain of programming skills. I think I'll go cry into my beer now. -ESpringer

Dear FMForums folk, I'm a long-time personal user, building and working out of my own solutions. (I used to post here often, but for years now have been happily using rather than designing...) One of my databases uses FM web viewers as a front-end for my university's moodle (cms platform), tunneling in based on various URLs for students, assignments, etc. In the past, a cms like moodle (and before it we had BlackBoard) would need me to authenticate once, and then all subsequent url queries within a session were served up properly, just as if I were using an ordinary browser. With more recent versions of moodle, suddenly authentication fails to survive any transitions at all (from one tab to another, from one record to another, toggling into layout mode and back). It's as if the browser session resets with every step of my workflow. I've just upgraded to FM19 and delayed posting to see if that would help, but I'm still getting the constant authentication roadblocks. What's preventing FileMaker from retaining authentication via PHP in the way that browsers do? (I'm running on Mac OS 10.14/Mojave, so it's emulating safari, yes? But Safari itself is having no trouble.) Many thanks for any ideas!

I'm late to this discussion, but like MSPJ I was considering upgrading to FMPA 14 precisely for the sake of improving my runtime projects -- developing solutions for individual desktop end-users who have no budget (nor enough tech savvy) for dealing directly with FileMaker Pro. In my case (since I have a totally different day job) the loss of Runtime would not send me to a competing platform; I'd just cease being the small-time not-really-for-profit developer that I've been. It's plausible that customers like me and MSPJ are not typical. I'm also hopeful FileMaker will offer us a relatively seamless transition to an FMP Go desktop-friendly solution. But I suspect that we (this minority of developers catering to non-corporate end users) will be taken into account only if we speak up.

Greg, In case you're still following this topic years later: I *HAVE* been working out of a custom FM front end for moodle (university web-based courseware), and have begun to set it up with an interface that would help other faculty adapt it for their uses. (I also developed a black-board based solution years ago.) Drop me a line or reply here if this is an ongoing interest of yours...

Dear lovely experts, Sigh. Several years ago I volunteered to help an artist friend by setting up a graphically complex database which made central use of a specific dingbat font (for musical symbols). Long story*** but... Now, on a much newer computer without the font, faced with a request to re-generate the printout, I don't even see how I can find out what the font name WAS, since FM only shows me the substituted-in font when I [convert and] open the file. (And, to be clear, there's no way of even approximating the desired output with a different font.) Somewhere in the file itself must be the name or other ID string for the font actually specified by my layout, yes?... Any tips? [***Kicking self for not punting out a pdf file way back when and creating an archive folder with all related files including fonts...***] Many many thanks in advance... -Elise

Holy smokes, LaRetta, I think it WILL work -- I'm now getting the List to sort according to the relationship's sort order, having made that sort order dependent on a calc field in which all the fluff prefixes get Substiuted by "". So far, looking good!!

Wow, Jeremy! Yes, something like sorting based on a derivedValue variable is what would be needed... Two thoughts: (1) A separate table of "fluff words" (prepositions and conjunctions) would allow prepositions to be added on the fly as they're encountered... there probably are less than a dozen, but it would make the solution more transparent if the list of words were accessible... (2) a calc field could display the variant of the modifier string with fluff words removed. So, the idea would be "return all values from field 1, but ordered according to an alphabetical sort on field2"... Would that be any simpler to implement? Actually, if I could figure out how to get LaRetta's angle to work... that is, getting List to sort based on Relationship order... then perhaps I could see this alternate sorting through without more custom function magic... But I suspect FMP will throw up objections to having t his relationship sorted on a calc field that it refuses to index... Alas, I don't have 12-advanced (just 12 pro trial), so working on the relationship-sort option means playing on a sandboxed transformation of my file that can't also benefit from Jeremy's custom function...

OK, now I'm really pushing my luck... but there's one niggling detail I had resigned to handling in a word-processor after exporting the data -- but perhaps Jeremy's function could be tweaked (for which I'd gladly pay an hour's wage!): The detail is that the list should be sorted by publisher's standards. There's a set of words (conjunctions and prepositions including and, as, in, of) that should *not* interfere with the alphabetical order of whatever comes afterwards. So, the image I posted isn't quite right. It needs to read more like this: blame accounts of the aim of... disadvantages of focus on... embraced by utilitarians... and expressive function... manner of enacting... and moral judgments... presumed practical efficacy of... as second-order responsiveness... unexpected outcomes of... If by chance Jeremy or anyone else sees how to tweak the custom function, great. Otherwise, it's a job to ask someone to do manually, painful as that is. Sigh. :|

Thanks all (and sorry I wasn't around to acknowledge quickly). Â Jeremy, that did the trick and FAST -- and it's why I thank my former self for springing for FMP Advanced! (I'm just making the decision whether to go 12 Pro or 12 Advanced after trial expires and this helps nudge me over the top ) Â For LaRetta: Here's an image of: at left, the effect I need (as produced by Jeremy's Custom Function). At right, the tables for the index... Â

Dear friends, nice to see so many familiar faces here (LaRetta, Lee, Vaughan, Ocean West...) after being away from my Developer Hobby for years... I've been compiling the index for my book, using three main related tables for * flagged locations in text * terms to be indexed * subheads under main terms I've successfully used the List function to get each term-entry record to include a list of all related subheading strings (where each subheading string includes a comma-separated set of associated page runs). So I can now output something that looks quite close to the formatted index I need... HOWEVER, the List function is returning a poorly-ordered list (neither alphabetical by subhead nor numerically ordered by page -- I'm not sure what's doing the sorting, perhaps record creation order, but even that doesn't quite make fit what I see). I've attempted to solve the problem by making a Value List (which I at least know how to sort), and then using a calc to show ValueListItems... but such a value list "won't work" in this case (says the alert dialog) because the calculated subhead text strings (the things I need to alphabetize) refuse to be indexed. (Also, I've had a rough time getting the ValueListItems function to behave as I wish it would, in general... even when a value list appears correctly in a drop-down menu, displaying the whole list on a layout, using that function, is giving me spotty output.) Isn't there a simple function with which FileMaker will take a list of ¶-delimited values and spit out a sorted version? (Am I not seeing something obvious? )

Just a hypothesis, folks: this may be a Mac-familiar behavior that's suddenly affecting Windows users. Looking at who has said what, that's my guess. Working on a mac, I'm used to killing unwanted style by hitting "undo" after pasting -- or, of course, defining fields in advance so as to reject style info. Your source application (for styled text on clipboard) may also make a difference -- some apps will copy style info to the system clipboard, but others won't.

Ten days late -- but just in case: Two things can snag sliding: (1) slide based on "all objects above" can get stuck by anything on the layout that starts above the thing you want to slide, including things off to the right outside the print area; try making slide up based on objects "only directly above". (2) check for small or invisible objects within the layout that are above or vertically overlapping with the sliding object. Using "select all" from layout mode will show you the corner-drag-boxes for all objects. Sometimes I discover some layout clutter left over from prior edits.

thanks, comment... Yes, I need simultaneous access. That is, the school needs a layout based on "kids" that neatly puts each one's respective parents, doctors, emergency contacts, etc. into visually distinct places (via unfiltered (v10) portals or merged fields). I do see how a temporary global field might work -- in the old fashioned portal-filter-workaround way -- for certain purposes, but not for this perspicuous printable/visually-scannable overview purposes... Or rather, I could juggle multiple global fields to help with ad-hoc filtering of parallel paths between between tables, but then I'd be back in the same boat I was trying to avoid! So, I've gotten over my fetish for "real data fields". It was a fantasy spurred by real advances in v10 and v11...

Bruce, "concocting" is not exactly a technical term. I was just referring to a field "cooked up" for no other reason than to make relationships work better. That is, a field that does not contain anything that could properly be called DATA either about the things themselves (the real-world stuff/people/relations/possibilities that a given table tracks) or about FM's internal tracking process (such as modification dates, record IDs, etc.) With portal filtering (and that "X" relation to join TOs), there's LESS call for doing things like making a field in each table whose job is just to stand there and hold "1" and so... I had gotten lulled into thinking I could build an elegant solution with hardly any such "concocted" fields. But I've woken up again. I promise to respect those little otherwise-meaningless fields that are indispensable to knitting the data together. :

Actually, I'm confused, bcooney -- it seems you suggest NOT having a join table for roles, but keeping roles in each person's records. But then for multiple-role people (of which I have lots -- up to six or eight for some people) do you use a repeating field, or return-delimited values, or what? More specifically, I need to be able to track that Adult 1 is Kid 2's grandmother with alternate pickup authorization AND that that same Adult is doctor to Kids 3 and 4, as well as first emergency contact for Kid 3. Because roles each index to a kid (not just to the whole school like "principal" or "school nurse" would), it seems to me that a join table is structurally better. No? For example, I need to be able to delete roles readily without deleting the adult data behind them. Surely that's much more efficiently done by deleting a join table record than by scripted surgery on a multi-value field in the adult record...?

Thanks, bcooney. It sounds like you're suggesting the second of my two approaches. Interesting that you're still making a field for that constant "1" anchor... It seems FM could have easily built us a variation on the universal join (is there a technical name for that symbol?) that just makes Boolean connections (any record to positive numeric values, positive to positive, positive to any). And otherwise I'm doing pretty much what you recommend, although I opted for putting address and phone right into adult records with repeating fields. It's not structurally ideal, but I also may need to pass this database on to even more novice FileMaker volunteers at some point, and I'd rather not have their eyes glaze over more than necessary... :)

I remember when we needed a dummy field with fixed auto-enter value of 1 for universal relations across tables, and I'm so happy we don't need that but... I'm feeling driven to do something similar, and hope I don't have to. Context: I'm volunteering a solution for a nonprofit preschool . Where their old flat-file solution had made mothers, fathers, doctors (and their contact info etc) into "properties" of children, I'm happily building a table of adults with join-table relations to kids -- parents of one may be emergency contacts of another, etc. Nice to have adult phone number changes reverberate through multiple roles, etc. (They're so impressed. ) Problem: There are lots of places where I need to make a calc or layout pull up "this kid's doctor" or "how many emergency contacts for this kid" data. Filtered portals are great on the fly, but (1) school is licensed for v10, not v11 (& I'm too amateur to be confident with baking them into a runtime yet); and (2) calcs (and merged fields, etc.) will still pull on the first sorted but unfiltered adult found. Right? I'm staring at the relationship graph, pulling up the relation definition between kid and "ThisKidsDoctor" join-TO, and WISHING I could just specify role-specific relations based on matching: Kid#=@Kid# AND "doctor"=Role. In other words, I want to build something one-sidedly filter-like into the relation itself. Instead, I resort to either: (1) concocting global fields in the kid table to help anchor each kind of role-relation (could I stack them into repeated fields? probably not for merge purposes, right?): one for the text "doctor", another global field to house "dentist" string, another for "emergency", another for "pickup". (2) concocting a boolean calc field that flags this and that relation (isDoctor?) and making a global "1" field in the kids table that can connect with this and that join-path as necessary. (But then the direct path to *create* records in the join table via that relation is blocked...) I really *love* having my fields reserved for... um... data. Is there an elegant solution I'm missing? Or suck it up? But which way?

First, *why* have you structured your solution around a repeating field, if the first repetition is to be open to data-entry, and the second is to function more like a calculation? If you could explain more clearly what your real-world task is, I suspect we might find that the best solution does not revolve around a repeating field at all...

Hi beeswax! I'm not at all an expert on this sort of database structure, but I can make one "common sense" remark about the structure as you describe it. That is, if you think about which *tables* you'll need, I think what you've written collapses two tables that should be distinct: "people" and cases. Perhaps it seems obvious that people will only have one case in the system at the same time, but you want a database structure that is robust enough to handle it if suddenly it turns out that one person has multiple claims in process. (And you might call that table "claimants" rather than "people" -- unless you want to make an insiders' joke about the humanity of the attorneys in your other table. : As you describe the task so far, it sounds to me as though *cases* will be the primary basis for your most crucial layouts, with related fields and portals to other tables. As for how to track communications and payments, I'd encourage you to go with related tables here, too. Although you may enter information into those tables only *from* a Cases layout, and a person using the database shouldn't need to *think* about the fact that the data is stored in a related table, there are various advantages to doing it this way (as with line items on an invoice). In particular, it will enable you to slice the data in different ways, such as being able to bring up an insurance-company-based layout on which you see (portal-based) info about all recent communications and payments with which they've been involved... I'm replying only because you haven't yet got another response. I have no doubt that someone will have something closer to an expert's advice...

Dearest Gurus, I've got an excel table whose *cells* (apart from row and column headers) *each* need a corresponding *record* in FM. (Each record would be based on three fields: rowheader, colheader, value) -- I'll then have more fields to add in FM9. Essentially, I want to convert the sheet not into an ordinary table, but into a saturated join table for two existing FM9 tables (one organized around the columns in the Excel sheet, the other around its rows). There must be an easy way... any hints?

mbsteed, can you say more? After all, 8.5 is supposed to work on Leopard apart from the 3 issues named on FM's site. Could you clarify: did you *create* the databases in Leopard, and then they didn't open? Or do you just mean that any existing databases don't open? Also, are you using any IWP or localization in your databases? For convenience, this is what FM says (in the article mentioned above) about FM 8.5 on Mac OS 10.5:

Thanks, tv_kid. Apparently FileMaker refuses even to *test* versions 8 and prior with Leopard. So, they don't even know whether there are more issues than the three they mention for FM 8.5? So, I'm still hoping some other adventurous souls, perhaps Leopard-users on this list who are upgrading to 9 anyway, will happen to be able to test out FM8 and report on whether the sky falls. :

Most discussion of the Leopard/FM fiasco is on the FM9 forum, but for those of us with FM8 destined to get a Leopard machine, is there somewhere we can turn for initial reports of what works, what doesn't and (gulp) what crashes? (I know it's not *supported*, but the question of course is how well I can limp along 'til I can afford FM9; my new machine is budgeted for me, but FM is "luxury" software...)