Creating Records through "MagicKey" global

[OlgerDiekstra]

I'm using the Selector-Connector principle to build my new DB, and use the "MagicKey" approach to create records where needed. One of the advantages of both methods is that I pretty much have no need to jump from layout to layout any more to create records. I have stumbled upon one issue though.

To create a new record in a table, I have two table TO's that have the same base table. I use a global field as a magickey. Whenever I want to create a new record in a table, I clear the global MagicKey field in the parent table, and then set a value in one of the fields in the child table. This works beautifully. Except when the table is empty (ie no records).

See attached DB as an example. There's a button next to the portal to create records. This won't work until there's at least one record.

Anyone have any idea why this is and how I can work around it? My current workaround is to create a small window when there's no record, change layout, create a new record, and then close the window again. I only need to do this if there are no records in the table so in most cases it's not much overhead, but I have hit a snag with this. I also use dialog windows at times (ie for changing the password or when a user wants to log off) and in that case I can't create another window over the top, and things break again.

The only other alternative is to ensure every table has at least one record, but that's again not very elegant.

testRecordCreation.zip

[bruceR]

The CREATOR table occurrence needs one record. Don't use the same table as the creator and target.

testRecordCreation.fmp12.zip

Edited August 31, 2015 by BruceR

[OlgerDiekstra]

Don't use the same table as the creator and target.

I did try using a different table to see if that made any difference. Any reason why its better not to use the same table?

The CREATOR table occurrence needs one record.

Bummer. I guess I'll have to make sure tables are populated with at least one record in that case. I know that foundcount() is empty when there's no record, so detecting it is easy.

[bruceR]

Why plural? How has any of this got anything to do with Connector Selector? One table allows records to be created in any other table.

[OlgerDiekstra]

Well, in itself this doesn't. But in my DB, I'm using the Selector-Connector principle to build the relations. I've omitted the Selector part though, and am using only the Connector. Though I may come back on that thought down the line.

Both Seedcode and Todd use the Selector to create records. I've decided that for now I'm taking a different approach and keep all related items together. Ie, all customer TO's are kept together, all invoices, etc. See screenshot for more clarification.

With Seedcode and Todd's, the creation TO's hang off a selector TO. For now I prefer to keep the creation tables with the customer TO's or Invoices, or whatever. It keeps everything together. (in the screenshot it looks a bit messy, but that's because I've moved some TO's out of the way temporarily). One of my reasons for doing it this way is to improve portability. All I would have to do is copy the entire TO structure for the customers, including associated scripts, CF's and layouts and connect it to a new connector TO.

Even if this turns out to be more trouble than its worth, changing it to a true Selector structure isn't that involved as the creation TO's just need to be anchored off a different TO, their name doesn't need to change and none of the scripts should need changing. If for no other reason, it creates a learning exercise for me.

So when you say not to use the same table as the creator and target (which I have been without issues so far), I figure there's a reason and would like to know, so that I can determine whether I should amend my approach.

Thanks Bruce!

[bruceR]

Looks like you're missing the whole point of the technique.

Selector is how records are created.

"So when you say not to use the same table as the creator and target (which I have been without issues so far)"

Isn't that exactly the issue you're writing about? It is NOT working for you?

I think more of the graph would have to be visible before any comment could be made on it.

[OlgerDiekstra]

Ah, despite being late here, the light finally came on. I didn't see the purpose of the selector part before.

Well, looks like I'll have to alter my setup a bit then. At least now I understand why its there, rather than blindly copying it and not understanding it's purpose.

Thanks for pointing that out!

[Justin Close]

Olger,

    I have run into this issue a few times with Selector technique as well:   you can't create new records in a table if there are no records in that table to begin with.  Yes, this is a situation where the Source and Target tables are the same base table.  The root cause of this problem is the fact that without a record, it can't evaluate the relationship to the Selector TO.  At least that's my understanding.  And this happens if you have a found set of 0, too.  So you can't fix it by just having ANY records in the table, but you have to be showing a record.

In my current scenario I ran into this problem because I am trying to allow for creation of new records on a layout.  Say, for creating a new Line Item for an Order, when the layout is based on Line Items.  If there aren't already Line Items existing (and shown in the found set), it won't work.  This cropped up because they can delete Line Items as well; if they delete the last one from the found set, then a new one couldn't be created.  (I got around this by checking if it was the last one being deleted, and just wiping that record clean instead of deleting; essentially forcing them to have a default line item.)

--  Justin

[OlgerDiekstra]

Olger,

    I have run into this issue a few times with Selector technique as well:   you can't create new records in a table if there are no records in that table to begin with.  Yes, this is a situation where the Source and Target tables are the same base table.  The root cause of this problem is the fact that without a record, it can't evaluate the relationship to the Selector TO.  At least that's my understanding.  And this happens if you have a found set of 0, too.  So you can't fix it by just having ANY records in the table, but you have to be showing a record.

That makes sense. I hadn't thought of found sets yet, as I'm developing a new DB, there aren't many records in there yet, and I haven't done a lot of finds in that DB as yet, so hadn't stumbled onto that as of yet.

 

In my current scenario I ran into this problem because I am trying to allow for creation of new records on a layout.  Say, for creating a new Line Item for an Order, when the layout is based on Line Items.  If there aren't already Line Items existing (and shown in the found set), it won't work.  This cropped up because they can delete Line Items as well; if they delete the last one from the found set, then a new one couldn't be created.  (I got around this by checking if it was the last one being deleted, and just wiping that record clean instead of deleting; essentially forcing them to have a default line item.)

--  Justin

I actually use the full Selector-Connector principle, using the selector table only for creating, deleting and selecting records. Because all TO's are connected through the connector table, and because the selector table always has one record, creating records is therefore not an issue any more.

I guess I needed to go through this exercise to understand why the selector table was necessary. Now that I figured that out, with the help of some pointers from Bruce, it makes it all much easier. I now have one script that gets invoked for all record creation/deletion in every TO, as well as selecting a record. I don't actually allow users to delete records anymore, they simply mark a record as deleted and then I can run a maintenance script on a nightly basis and purge deleted records after a month or so. If somebody accidently deletes a record, I can be a hero by restoring it in a heartbeat. One benefit is that I can now also keep track of who deletes what. My TO relations filter out all deleted records, so normally, they won't ever show.

One big benefit I found of the Connector-Selector method, is that layout elements can easily be copied from one to another without needing any TO adjustments. Ie, a popup  that creates or edits a person, can be copied and will work immediately.