TheLagosChap

Thank you Joshua (and Jesse). That re-opens the option for us. We still need to think about the (obvious) need to have the modification timestamp field updated for the record to be considered in the sync. But if our calculated field value changes due to a field value change in another table, the modification timestamp will have not been updated and the record will not sync. So it appears that to be sure any changed calculated field value is synced, we would have to forcefully modify the modification timestamp for ALL records in the table. That would defeat the principle of "sync" since it would be equivalent to transferring the entire table every time. So, some more brainstorming to do on our end it seems... But thank you for the offer of a trial license! I will surely get back to you on this in case we want to explore this option further.

Thank you Joshua. I understand and it's not what I hoped for but what I expected We're actually trying to expose FM data to Microsoft Power BI and of course the unstored calculated field values don't get there by themselves. So we were thinking of putting a database in between (another FileMaker or some SQL - with stored data fields only) and use MirrorSync to transfer from solution's database to the intermediate source for PowerBI (transferring unstored calculations into stored data fields). Since we'd rather not duplicate all fields if possible (it's going to be a big database already), maybe we should use instead some native FileMaker scripts that will have access to the unstored calculated fields values to transfer between the 2 db... Thanks for your help!

Hi Forum, Can anybody point me to the right direction on getting FileMaker unstored calculation field values synced into (any sort of) SQL database (one way sync)? I understand that unstored calculation fields do not contain any stored data and their value can be dependent on a Filemaker context (including relationships, global variables etc)... Rather than scripting some export from FileMaker itself, could MirrorSync do the job somehow? Thank you Serge.

Hi Is there any way (Applescript, SQL via Filemaker ODBC or anything else) to automate the modification of a Filemaker database schema? I know that SQL CREATE TABLE and ALTER TABLE are possible but I am also looking at creating/modifying RELATIONSHIPS between tables. I do not think this is possible via SQL. The purpose is to automate the upgrade of a Filemaker database (vs. repeating each change manually using the "Manage Database..." dialogs). Thanks!

Unfortunately there is no such option/tool at the moment. I recommend you look into the Separation Model. Updating a solution (UI, Business Logic) without changing the user's data one of the benefits of that design approach. There's a separate topic in the forums dedicated to the Separation model.

Hi, I've been reading a lot recently about the separation model as I would like to develop a Filemaker application that needs to run on a (slow) WAN. I have found one "purist" approach that aims at avoiding anything else than just data in the data file, avoiding anything like calculations, summary fields etc This requires what I see as significant cost (work) in the User Interface file in order to reproduce the same functionality (additional local tables, fields, relationships, even mirror tables...) A very good example of this is shown in the videos from filemakermagazine (for example "The Separation Model - Breaking up your data and interface" but there's more recent ones as well). What I have not been able to find anywhere yet is the actual reason WHY I should not put calculations, summary fields etc in the data file... Thanks, Serge.

WORKS! Thank you! I thought I knew a few things about Filemaker but I'm happy to say we all learn everyday.

This sounds a very promising idea! I'll try it out and revert. Thanks!

OK, I see I'm not very clear, sorry about that. It's partially because I'm trying to simplify the description of the problem (and reduce the number of tables & relationships to get there) I suppose... A machine can produce any number of items. A factory can also produce any number of items (but without any link to which item can be produced by machines). When placing a machine into a factory, the intersection of the items that can be produced by the machine and the ones that can be produced in the factory is what can actually be produced. Or explained otherwise, the list of possible items produced is constrained by the list of items that a factory can produce and the list of items that a machine can produce. That's the (value) list I'm trying to display. Hopefully this is more clear? Thx for taking the time!

All Items are in one table (Item). The 2 other tables have relationships to the Item table. And sometimes, I need to see which Items can be produced by a Machine X that has been assigned to a Factory Y, i.e. the Intersection of both.

Hi, I think I've seen a similar problem posted recently (subject "Intersections" by eric.p on 10-02-09) but no useful answer. So am trying again, rephrasing the problem, it might help. I'd need a way to show a value list with records from one table that are linked to 2 different tables (it's an intersection indeed). There's no way to do this in the "Value List" interface, hence me posting this in the "Relationships" section. To be more practical, if the description above is too theoretical... I have an "Item" table linked to a "Factory Items" table and also to a "Machine Items" table. These 2 last tables store which Item can be produced in a particular Factory and by a particular Machine respectively. Now I want to have a Value List showing me which Items can be both produced by in Factory and by one Machine (i.e. Intersection of the Items records linked to Factory Items and linked to Machine Items). Any interesting way to do this automatically (i.e. not via a script that will, each time, create records in another table with the resulting intersection)? Thanks!

I think I understand what you mean. "_" would be some kind of "operator" influencing the search result. It's just weird/sad it's not in the popup list of symbols. I'm going to use another way then. But thanks a lot for the hint. You saved me from the mental institution :-)

Hi, I'm experiencing a rather weird behavior which I can't explain so far, hopefully sombedy out there can help. I have a text field containing "month periods", with the content formatted as YY_MM, like: 07_01 for January 2007 07_02 for February 2007 etc I'm trying a very simple "Find" on that field with something like: ">07_05" to give me all periods after May 2007. This returns 07_08, 07_09, 07_10 etc but NOT 07_06 or 07_07. I've tried to: - find with the condition >"07_05" (with quotes) - recover the file - optimise and compress the file Nothing makes a difference. Obviously, if I add a calculated field in that table with the same condition (>07_05) it correctly returns true (or 1) for values like 07_06 nd 07_07. Other similar finds (like <07_06) return also some similarly wrong results. I've even tried to create a new database document from scratch with only one table and one field like this, I get exactly the same wrong find results!!! The same (apparently wrong) behavior happens on a local 8.5 Advanced and on 8.0 server (both on Mac OS X). Either it's a bug or I'm really missing something big. Any clue or confirmation of this bug will help me staying away from a mental institution. :-) Thx, TheLagosChap.

I've seen a lot of interesting hints in all the links provided. I now need to spend the time and analyse but it really looks like one of them will do. Well done - Thanks!

Hi sd, I'm talking about relationship between 2 tables (for instance to display a portal) rather than summary in a report layout. Sorry about the confusion. Actually, I want to "simulate", in a portal, what the summary function can do in report layouts. Not sure what you mean by "duping the TO" but, if I understand well, you suggest to use 2 relationship (and 2 layouts, 2 portals). It's not exactly what I'm looking for. Actually, I'm looking for a shortcut to avoid creating 2 instances of the same table and 2 different relationships, one with one criteria "Item Code = Item Code" and "Shop code = Shop code" and another relationship with only "Item Code = Item Code" (allowing to view the related records for all Shops). This would work very well but, creating one table view/instance and one relationship is "too much work". In fact, for the simple example I described it would be fine. But I'm actually looking for something I can implement for a combination of at least 4 criteria. Using the "natural" solution you propose, it would force me to create plenty views on the same table with each having its own "specialised" relationship. Let's say for instance I have 4 criteria fields (A,B,C and D), I would need to create one relationship for each combination, like: 1. A=A and B=B and C=C and D=D 2. A=A and B=B and C=C 3. A=A and B=B and D=D 4. A=A and B=B 5. A=A 6. B=B and C=C and D=D 7. B=B and D=D 8. B=B etc I hope I can make one relationship that includes all fields and, dynamically, turn some "on" or "off". Like I said, this could be achieved by putting some kind of "catch all" value in some of the fields or by dynamically changing the relationship operator (from "=" to "X" and vice versa). So, in my example, I would have only one relationship (A=A and B=B and C=C and D=D) but would put some "wildcards" in the fields I want to "turn off", like: 1. A=A and B=B and C=C and D=D 2. A=A and B=B and C=C and D = "any" 3. A=A and B=B and C = "any" and D = D 4. A=A and B=B and C = "any" and D = "any" etc or, dynamically change the relationship to, for instance: 1. A=A and B=B and C=C and D=D 2. A=A and B=B and C=C and D x D 3. A=A and B=B and C x C and D = D 4. A=A and B=B and C x C and D x D etc I hope this is more clear. Thx.