Selector not releasing created record after clearing the matching selector::myID_g field

Questions
1

I am working in a system where the original developer used selector-connector.

I want to use the selector to implement the creation of multiple records in a single transaction (records are created via the relationship, clearing the selector::myID_g field between record creation, no commit until I am done with all records to create).

My problem is that I was assuming clearing the content of the selector::myID_g field would release the created record from the relationship even if I am not on a layout based on the selector table occurrence (the selector is available from all the graph via the connector).

Because the record is not released, I end up overwriting my record instead of creating new records. The same sequence of script steps running on the selector layout gives me the expected behavior (clearing the global makes it so there are no records across the relationship, writing to fields across the relationship creates a new record, that automatically populates the global field with the new key).

Is this normal or is this some type of bug? What am I missing?

Thanks for any help with this.

1 Like
2

Hello @Bobino,

Happy new year.

I used to try to remain educated with respect to Selector-Connector, but I don't actually use it.

That said, you've done a great job of describing the scenario, and I can relate to it (no pun intended) just from a general sense of FMP and how it tracks and refreshes (or doesn't) relationships.

In particular:

The scenario where a relationship spans two hops, and the second hop employs a predicate which references a global field belonging to the intermediary table (i.e. the middle table of the three chained TO's).

This is a beautiful and crafty setup which requires keeping in mind that extra steps are sometimes required to refresh the relationships, in order for all the data to fall in place as we expect and/or desire.

To be a tad more concrete with an example:

  • Given tables and table occurrences: A, B, C
  • And a graph that relates A to C, with B in the middle, i.e. A --- B --- C
  • And the relationship from B to C involves a global field from B

Then:

  • Most of what we might do while in the context of B will behave as we expect, with no extra effort.

This is analogous to how you were able to create multiple child records in your transactions when the script was executed from the "Selector" context.

But, when sitting on the context of A, and wanting to manipulate or display data in C:

  • I've always found it necessary to let FMP know if and when the global field in B is updated
  • Not doing so can lead to displaying or acting upon the wrong data.

This is analogous to how, while in your non-selector context, the data was getting over-written on one single record when what you had wanted to do was create multiple child records within the transaction.

In order to work around these types of scenarios:

I've always set up the relationship from A to B with a dummy predicate that I can nudge in order to tell FMP to refresh my relationship path. Kudos and credit goes to Daniel Wood (@weetbicks) for sharing this technique (back in the days of around FMP 11, IIRC) -- thanks Daniel!

Nudging the relationship in this way always worked for me when I needed to display accurate child data from C while on the context of A.

My hunch was that the same nudge approach could possibly help with the scenario that you posted. I tested it just now, and it seems to work.

Here is a file to illustrate:

TransactionRelation4Bobino_20190102B.fmp12.zip (72.7 KB)

Please let me know if the file is not clear enough about the approach, and I'll attempt to clarify.

With respect to whether or not you actually wish to employ such an approach:

I could see this getting a thumbs down if the relationship graph is already a tricky or complex one. Adding the pieces needed to nudge the relationship could be the straw that breaks the back of the Camel of Complexity. I'll note that I did briefly try to see if I could refresh the relationship without the use of modifying the graph (instead using script steps such as Refresh Object and Refresh Portal on using dummy fields/portals from C), but my cursory attempts down this path did not work out.

Finally:

I hope this helps. I realize that this explanation is rather lengthy. Sorry about that -- hopefully it is methodical enough to follow, and/or the example file will help connect the dots.

Kind regards,

-steve

p.s. Your description of what you encountered was great. Thanks for making it so clear.

3 Likes
3

An active refresh (Window) with update of related data is recommended. I always force a refresh/update when data relevant to a relationship changes.

4

An update to the post I added above:

I was troubled by the idea of adding extra field and (cartesian) relationship requirements to a potentially complex graph in order for this to work.

I decided to look to see if the relationship could be nudged without a dummy constraint added (as in the sample file that I posted), and simply by re-setting a key field in the first TO to its current value.

Here is a re-worked version of the sample file that I posted above, which uses this approach instead of the dummy constraint.

TransactionRelation4Bobino_20190102E.fmp12.zip (72.3 KB)

Brief testing suggests that this approach may also work.

@Torsten : I should mention that I did experiment with using Refresh Window (flush cache) as a possible approach in the sample file that I posted above. I experimented with Refresh Window, Refresh Object, and Refresh Portal. I was not able to successfully get these to do what nudging the relationship seems to do. I won't go so far as to assert that these won't work (especially since it makes sense that they might, and especially since my experimentation was brief), but I will offer that my attempts were unsuccessful.

3 Likes
5

@steve_ssh, forced refresh works flawless in the solutions I have built but it may not in a particular context. I have seen things not working as supposed without being able to find the root cause.

1 Like
6

Creating a record through a relationship (allowing creation of records through this table via this relationship) creates a record but does not commit it. FMPA can only have one record at a time open for a table in given TO on given layout. This is why it overwrites the uncommitted child record when looping through data for transfer to individual child records.
Did you try a commit in the looping routine? I am aware that this breaks transactional logic, which needs to be scripted in addition, as FM does not offer this by default.

7

@steve_ssh thanks for your detailed explanations and demo files. You got everything right. In my case, I won't need to create additional predicates in the relationships because every relationship from a distant context to the connector is already using a cartesian relationship.

My setup is like this where there are obviously many different 'distant' TOs and 'target' TOs:
distant → connector → selector → target

'Nudging' the relationship between distant & connector by setting distant::cardinal to anything (on top of the set field happening in selector) works in my case.

Unfortunately, this means my script needs to rely on a fixed name for the variableTO::cardinal field because I am now using a SET FIELD BY NAME step (to allow my script to be called from many different distant TOs.). This still is better than having to create a new window and navigate to the selector context.

In terms of design, let's just say I have a strong preference for the transaction setup I saw in Karbon, but I'm not going to re-write this system and figured selector-connector would give me something similar (turned out to be only partially true).

As a side note: I will never complain about a lengthy reply, specially not one having a demo file attached. I'm sure it is easier for someone to answer in such a way when they feel good about the question being clear in the first place, but all in all you took some risk, putting this much effort into your answer (I'm pretty sure asking the question was much shorter than the time you spent to set this up). So I am very thankful for that level of commitment on your part.

@Torsten I had tried the refresh approach, but could not get it to work. If you get that to work with @steve_ssh files, I would be curious to see.

To all: I know FileMaker may have a difficult time tracking what happens in TOs that are a couple hops down the graph, but this felt like a bug. More so, as I was debugging this, instead of clearing the global, I wrote a random value to my global (one that would not exist as a key in the target TO) and my connection to the original record persisted even as I was writing to the record, without this re-writing the key of the record itself. It is like a direct bond gets created and simply disregards the field values that are involved in the relationship.

So I guess my question becomes what is Claris position on this behavior (normal, bug, ...)

8

The tricky part here is when you are doing more than one hop away. If it was just one hop, you could use the global they way you described.

For something like this, I will usually just put portals on a single layout and jump from portal to portal creating records as needed. That way you can get the performance benefits of only having to commit once.

9

Forgot to answer that part. Committing the records was not an option because as I was failing at every record creation but the first one, it would mean I would need to commit at each record, something that was against the pattern I was going for (having all created records in a single transaction).

10

Hello @Bobino,

Thanks for the reply, and I am glad to know that the immediate issue has a solution.

Regarding the laziness of resolving relationships with respect to "distant" changed data:

I agree that this feels unexpected. For me, perhaps because in all the more common use cases the FMP relationship resolution works so well - transparently, and as expected. That said, I am mostly used to it, and I just try to understand (and remember) what I need to look out for.

Regarding needing to follow a naming convention for a field in order for the script to work across various contexts:

My thought would be to consider playing around with parsing the output from RelationInfo to dynamically determine the field name that you need to nudge the relationship. Depending on how much effort is put into it, I believe it might be possible to have it so that nothing need be hard-coded or based on a naming convention.

Your parsing routine would need to know the name of the Connector TO, but this can be obtained dynamically via GetFieldName, and it would also need to know the current context, but, of course, this is available via Get( LayoutTableName ).

I haven't actually tried this, so there might be some obstacle that I am overlooking, but I am inclined to believe that all the parts and pieces are there to determine the field name dynamcally.

To be clear, the assumptions I am making here include:

  • There is only one single Connector TO, to which all Distant TO's connect

  • The overhead of the RelationInfo function is small, even for a big graph with lots of connections.

I used to use RelationInfo in the Data Viewer a lot on a solution with a very busy and connected graph, and I never noticed any performance issue -- but, this would certainly be something I'd want to keep an eye on if I were trying it out in a script that a user would be running.

All the best & kind regards,

-steve

11

This is indeed a suitable approach when the script runs on a specific layout. One can go to the last portal row to create the new record instead of clearing the global field. This ease has the price of requiring layout objects. I'm not trying to say one is better than the other, in this case, it really depends on the preferences of the person behind the keyboard. (That said, if anyone feels like one approach is best, I would like to hear them).

1 Like
12

Agreed. The complicating factor here is the number of relationships between the layout TO and the record creation. It can be done, but to me, for this scenario, feels a little fragile.

The selector-connector model, by definition, means you should be able isolate each TO to create the records using only the one hop. I'm not entirely a fan of the model, except for that type of processing. You simply choose which TO::field to hit to create the records. The script then handles the clearing of the global, and you are never more than one TO away while creating records.

13

Very nice solution, @steve_ssh!

Out of curiosity I mishandled it a bit. Here's what I found:

Local host:

  • kill FMPA app via terminal: complete rollback
  • end FMPA app through 'Quit FMPA' menu command: uncommitted records persist (I guess its FM's auto-commit that comes into play)

Server host:

  • kill FMPA app via terminal: complete rollback
  • end FMPA app through 'Quit FMPA' menu command: uncommitted records persist (I guess its FM's auto-commit that comes into play)
  • kill fmserverd via terminal: complete rollback
1 Like
14

An important thing to remember when using portals to create rows is that, if the portal doesn’t have the scroll bar enable, you can only create as many related records as you have rows visible in the portal. That surprised me, but if guess the logic is “you aren’t allowed to go to the next row” so there is no additional blank row from which to create a record once you’ve used the visible rows.

5 Likes