Relating tables

The only success I have had when relating two tables is to set them up as follows:
Table A has primary key A and a field called course. Table B's primary key is course. So the tables match on course. The relation is One matching record, foreign key at the related table.
​
There are a couple of problems with this:
If Table B's primary key is course, I can't add new course records to that table - unless someone can tell me how to do it.
Then If I give Table B a different primary key, the only way I can match the two tables is if I add a field to Table A that contains the primary key value from Table B. This option is a problem because I would have to completely reload the table and it is large.
​
Any suggestions?

Well yes if you want to relate two tables, they need to share a field of some sort.
​
Why wouldn't you be able to add new course records if course was the primary key?

Thanks for the response. Perhaps I wasn't clear. The two tables have a shared field, but the field in Table B has to be the primary key. It won't work if it is just a field in the table. What an I doing wrong?
​
You tell me why I wouldn't be able to add a new course if course was the primary key. Again, what am I doing wrong?

Why wouldn't it work sorry? No reason why it shouldn't.

Let's start with relating the two tables:
Read function:
Primary model:Info
Database table: userinfo
primary key:aid
pertinent field: username
Next model: Users
Database table: Joomla users table
primary key:id
pertinent field: username
Related to Info, One matching record, foreign at the related table
Foreign key: username
Apparently this doesn't work because I am try to display fields from both tables in a table and only get fields from Info.
​
Now about not being able to add a record when I am adding in the primary key:
The primary key of the games table is PlayDate (this is really a text field).
Index event:
{view:add_games_button} calls add_games event
add_games event:
{fn:read_one_games}
{view:add_games_form}
Read function: read_one_games
Model: Games
Table: games
Where: Games.PlayDate:{data:games_playdate} (games_playdate is defined in a link view to be games_playdate={var:games_table.row.Games.PlayDate}. This view is used to call up and update form and sequence.)
{view:add_games_form} calls save_new_games event. Form content is {view:games_fields}
save_new_games event:
{fn:save_new_games}
{fn:check_save_new_games_result}
save_new_games function
Model: Games
Table: games
check_save_new_games_result function
Data provider: {var:save_new_games}
​
I get a message that the data was saved successfully, but nothing was saved.

Do the relationship manually. Model.field:model2.field2

For the other one put {debug:} at the end of the event and post the result

Where do I code the relationship manually?
​
Here is what the debug returned:
​

Array
(
    [save_new_games] => Array
        (
            [data] => Array
                (
                    [PlayDate] => 04/10/2019
                    [DisplayDateLong] => Thursday, May 9, 2019x
                    [GameTitle] => PARTNERS' BETTER BALLx
                    [Description] => 
                    [Format] => Please list players in partner order (1 and 2, 3 and 4).x
                    [Awards] => NET
                    [Entry-Start] => Monday, April 15, 2019x
                    [Entry-End] => Thursday, April 18, 2019x
                    [notify-date] => 1/25/19
                    [modified] => 2019-01-21 01:03:42
                )

            [_success] => Data saved successfully
            [log] => Array
                (
                    [0] => UPDATE `games` AS `Games` SET `DisplayDateLong` = 'Thursday, May 9, 2019x', `GameTitle` = 'PARTNERS' BETTER BALLx', `Description` = '', `Format` = 'Please list players in partner order (1 and 2, 3 and 4).x', `Awards` = 'NET', `Entry-Start` = 'Monday, April 15, 2019x', `Entry-End` = 'Thursday, April 18, 2019x', `notify-date` = '1/25/19', `modified` = '2019-01-21 01:03:42' WHERE `PlayDate` = '04/10/2019';
                )

            [var] => Array
                (
                    [DisplayDateLong] => Thursday, May 9, 2019x
                    [GameTitle] => PARTNERS' BETTER BALLx
                    [Description] => 
                    [Format] => Please list players in partner order (1 and 2, 3 and 4).x
                    [Awards] => NET
                    [Entry-Start] => Monday, April 15, 2019x
                    [Entry-End] => Thursday, April 18, 2019x
                    [notify-date] => 1/25/19
                    [modified] => 2019-01-21 01:03:42
                )

        )

    [check_save_new_games_result] => Array
        (
            [finished] => 1
            [var] => 
        )

)

On the second model, in the "relation conditions" field

OK, coding the relationship manually worked. Thanks.
​
How about my other problem - adding a record when I am adding in the primary key?

If you are saving the table1 record, and then saving the related table2 record in the same event, then the newly created id will be available under {var:save_data1.id} , replace save_data1 with whatever your main/first save data action is called and replace id with whatever you've called the id

Sorry, I probably misled you. I am not trying to save a related table. This table is standalone. I just need to be able to add new records which will include a primary key that I type in - not an auto-incremented value.

Well then that should be fine too. Just obviously you'll have to make sure the key doesn't already exist before trying to insert a new record.

The example of the array I gave above is a case where I am trying to add a new game to my table. The key is the PlayDate (a text field, not really a data field). I'll show the debug output below. As you can see the save appears successful, but the data is not being added. The 04/10/2019 key date is not already in the table.
​

• new game saved successfully.

Array
(
    [option] => com_chronoconnectivity6
    [cont] => manager
    [conn] => games-copy
    [event] => save_new_games
    [Games] => Array
        (
            [PlayDate] => 04/10/2019
            [DisplayDateLong] => Thursday, May 9, 2019x
            [GameTitle] => PARTNERS' BETTER BALLx
            [Description] => (Maximum 8 strokes difference) Strokes as they fall.x
            [Format] => Please list players in partner order (1 and 2, 3 and 4).x
            [Awards] => NETx
            [Entry-Start] => Monday, April 15, 2019x
            [Entry-End] => Thursday, April 18, 2019x
            [notify-date] => 1/25/19
        )

    [button9] => 
    [joomla_user_state] => logged_in
    [fbf5dec8f22e24aad4cfb326c97dac02] => 68edfa43bc19fdad99e459b63731c291
    [jSGCacheBypass] => 1
    [bfb7d2eabf8f16fa44075f6377b21f00] => ed190103ee986d5c3a2d75c1bba7cfd6
    [a7a30c8924ebcb7d79f75acf9aff65c5] => 1
)

Array
(
    [save_new_games] => Array
        (
            [data] => Array
                (
                    [PlayDate] => 04/10/2019
                    [DisplayDateLong] => Thursday, May 9, 2019x
                    [GameTitle] => PARTNERS' BETTER BALLx
                    [Description] => (Maximum 8 strokes difference) Strokes as they fall.x
                    [Format] => Please list players in partner order (1 and 2, 3 and 4).x
                    [Awards] => NETx
                    [Entry-Start] => Monday, April 15, 2019x
                    [Entry-End] => Thursday, April 18, 2019x
                    [notify-date] => 1/25/19
                    [modified] => 2019-01-21 22:41:47
                )

            [_success] => Data saved successfully
            [log] => Array
                (
                    [0] => UPDATE `games` AS `Games` SET `DisplayDateLong` = 'Thursday, May 9, 2019x', `GameTitle` = 'PARTNERS' BETTER BALLx', `Description` = '(Maximum 8 strokes difference) Strokes as they fall.x', `Format` = 'Please list players in partner order (1 and 2, 3 and 4).x', `Awards` = 'NETx', `Entry-Start` = 'Monday, April 15, 2019x', `Entry-End` = 'Thursday, April 18, 2019x', `notify-date` = '1/25/19', `modified` = '2019-01-21 22:41:47' WHERE `PlayDate` = '04/10/2019';
                )

            [var] => Array
                (
                    [DisplayDateLong] => Thursday, May 9, 2019x
                    [GameTitle] => PARTNERS' BETTER BALLx
                    [Description] => (Maximum 8 strokes difference) Strokes as they fall.x
                    [Format] => Please list players in partner order (1 and 2, 3 and 4).x
                    [Awards] => NETx
                    [Entry-Start] => Monday, April 15, 2019x
                    [Entry-End] => Thursday, April 18, 2019x
                    [notify-date] => 1/25/19
                    [modified] => 2019-01-21 22:41:47
                )

        )

    [check_save_new_games_result] => Array
        (
            [finished] => 1
            [var] => 
        )

)

Hi kkurtz,
​
I don't think that you can update a record that doesn't already exist - you need an INSERT in that case.
​
You can check by copying the SQL from the debugger into PHPMyAdmin and see if it works there.
​
Bob

Not updating
Trying to add. Doesn't work

      [_success] => Data saved successfully            [log] => Array                (                    [0] => UPDATE `games`

right there, it says "UPDATE" so you probably have it set to update or to auto. Change it to INSERT.

OK, the light dawns!
I think I didn't realize you had to choose INSERT in the Write Action because I have always been adding records to tables with an auto-incremented primary key and have always used the Auto Detect write action. Just below the Write Action box there a sentence that reads "Select the whether to insert or update or let it be decided based on the primary key value passed and the update conditions." Seems to me that since I have been passing a new primary key value, the Auto Detect should have kicked in and recognized that it was a new key and therefore inserted the record. Apparently not so. Is this a bug?
​
At any rate, thanks for the help.
Karen

Me again, sorry.
Let me make sure I understand :
If I am trying to add a new record with a new primary key value, the Write Action has to be set to INSERT.
If I am updating fields in a record which are not primary key fields I can set the Write Action to Auto Detect and it updates those fields.
However, if I change the primary key with Write Action set to Auto Detect the code generated is Update and nothing happens because it is trying to update a primary key that isn't there yet. The same thing happens when I change the Write Action to UPDATE.
​
So how would I update (change) the primary key value? Do I have to insert the new record and delete the old one?

Try setting it to "Insert - duplicate key update"

I tried that. It added a new record with the new key and left the old record in the table.

OK do it this way then.
​
Read Data where primary key is whatever it's supposed to be. Set the select type to First matching.
Under FOUND put a save data set to update
Under NOT FOUND put a save data set to insert.

OK, I have a Read Data action with a where condition to find the record with the primary key in question and set to First Matching. On the Events tab of the read action both the Enable the record found option and the Enable the Record not found option are checked.
Then I have a Switch action where the Data Provider is (var:empty/read_action} and in Values setup:
true:{fn:save_new_games} set to INSERT
false:{fn:save_games} set to UPDATE
​
So when I change the primary key, a new record with the primary key is added. But what I really want is not to add a record but change the one that is there with a new primary key.
With the setup I just described if I change a field in the record and leave the primary key alone I get a duplicate keys error.

You don't need a switch action. There's already a found and not found event to drag the actions into.

also {var:empty/valname} is the wrong syntax it's {var/empty:valname}

Hang on you want to change the primary key of the record? Then you need to UPDATE it. Not insert or auto or any other option : update.

First, where would I find the found and not found event?
Next, I'll change the syntax.
Finally, I have tried the UPDATE setting and it didn't work because it is trying to change a record with a primary key that isn't there yet.

​
​
​

But what I really want is not to add a record but change the one that is there with a new primary key.

I admit to being a little punchy, but I am working in ChronoConnectivity 6 and there is no such found/not found action that I can find. I think you are showing a screen from ChronoForms.

Rightio then yes, a switch is warranted. {var/empty:read_data_name}
​
The highlighted quoted text is still a valid point though. Do you want to UPDATE an existing record or do you want to INSERT a new one?

I want to update an existing record.

Well what do you mean by saying the primary key isn't there yet

Suppose a record was added at an earlier time and the primary key had a typo. So it needs the be corrected.
If I call up the record with the erroneous key and correct that key, that is essentially a new key - one that isn't in the table yet. So the update doesn't work.
​
So the Switch with the not found the way I described above inserts the record, but the original with the erroneous key is still there.

What? You search for the old key obviously. Update where primarykey:oldkey

Don't understand how to tell it "primarykey:oldkey"
​
I click on the table listing on the old key to call up the update form. Change the key
Right now the update conditions is primary key: new key from the form.

No you put primarykeyname:newprimarykey in the "data override on update" field. In the update conditions you need primarykeyname:oldprimarykey
​
Put the old primary key I'm a hidden field or get it from the URL