Forums

Hi Bob/Max,

Does the query on work for table with primary key?

What happens is that I have a CC form with a link (/index.php?option=com_chronoconnectivity5&cont=lists&ccname=LedgerDetail&act=view&gcb=id).

OnClick the link, it is supposed to bring up a list from a table called Ledger (this table does not have a primary key and allows multiple records of same id).

When I set up the CC form LedgerDetail for testing with the following query:
<?php
$form->data['gcb'] = '2';
$id = $form->data['gcb'];
return array( 'Ledger.id' => $id );
?>

1. View Connection - list is created ok with the sql statement looks like 'WHERE 'Ledger'.'id' = '2'
2. But when I tested it directly from the localhost site, it throws out 1064 syntax error as follow:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '` = '2'' at line 1 SQL=SELECT `Ledger`.`id` AS `Ledger.id`, ....
WHERE `Ledger`.` = '2'

See the `id` is missing from the WHERE clause.

Not understand why it works in View Connection but not other way.

Thanks,
kwom

Hi kwok,

Please try

return array( 'id' => $id );

that might work - but some times it's hard to get the quotes correct :-(

I'm not sure that the ChronoForms code will work reliably on tables without a primary key. Is there some reason why you can't add one?

Bob

Hi Bob,

The purpose of the ledger table is to record what has been changed to an order (like a time line) throughout its life. So, say order id 123 could be recorded multiple times in that table. The query will be based on order id. If the order id is the primary key, the adding of same order id will cause sql syntax error.

Thanks,
kwok

Hi kwok,

Yes, I understand but that doesn’t stop you having a different column e.g. xxx_id as the autoincremented primary key.

Bob

Hi Bob,

1. I tried "return array( 'id' => $id );" - sorry, not working

2. I then added a auto-increment primary key xxx_id to the table. I added several test order id to the table and xxx_id is found to increment by one: 1, 2, then 3.

3. I then run the test in View Connection with the following code:

<?php

$form->data['gcb'] = '2';

$id = $form->data['gcb'];

return array( 'Ledger.id' => $id );

?>

debug information indicates the sql statement looks like 'WHERE 'Ledger'.'id' = '2'

4. I tested it directly from the localhost site, it throws out 1064 syntax error as follow:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '` = '2'' at line 1 SQL=SELECT `Ledger`.`xxx_id` AS `Ledger.xxx_id`, ....

WHERE `Ledger`.`xxx_id` = '2'

5. I empty the Conditions section or use other selection criteria, the debug indicates the query is `Ledger`.`xxx_id` = '2'.

No clue....

By the way, can I use chronoform to load a list? If so, can you provide some guidelines?

Is there a way to open chronoform and chronoconnectivity form inside a new browser window?

Thanks,
kwok

HI kwok,

Why are you setting $form->data['gcb'] = '2' - that is a string - and not $form->data['gcb'] = 2 - which is an integer.

You are making this more complicated than it needs to be.

can I use chronoform to load a list? - Sorry, I don't know what that means? What exactly do you need to do?

Bob

Hi Bob,

Sorry for the confusion.

I set it to integer 2 and the end result is the same. What I observe is that when it is at View Connection, the conditions set is honored. But when it is tested from the real deal {chronoconnectivity}LedgerDetail{chronoconnectivity}, the conditions is not honored and the gcb passed (e.g. order id) is used by the primary key (xxx_id) to construct the query.

I heard that chronoform forms can load multiple records to make a list (through custom code) but I never try it before. That's why I wonder if you have some nice reference I can follow.

If i want to open a chronoform forms or CC list inside a new browser window, do I use a customHTML and set the <a>.... target="_blank"</a>? What are the other ways?

Thanks,
kwok

Hi Bob,

(A) I have tested a bit more and have the following observation:

1. For an url with the gcb (e.g. /index.php?option=com_chronoconnectivity5&cont=lists&ccname=LedgerDetail&act=view&gcb=id) , CC v5 automatically tries to "associate" it with the primary key of the target table.

2. For an url without the gcb, CC v5 tries to run based on the query conditions.

(B) This forum topic: http://www.chronoengine.com/component/chronoforums/posts/t93448/p321782.html
mentions about sending other variables besides gcb on a CC url. Would it work in my case?

/index.php?option=com_chronoconnectivity5&cont=lists&ccname=LedgerDetail&act=view&val=orderid&fld={orderid}

Thanks,
kwok

Hi kwok,

I don't know what your case is so I can't tell if it will work for you - I would expect that a custom link could pass any data that is available to you.

Bob

Hi Bob,

/index.php?option=com_chronoconnectivity5&cont=lists&ccname=LedgerDetail&act=view&val=orderid&fld={orderid}

This construct will never work.

I find this forum very useful about passing all kinds of variable to a cc form:
https://www.chronoengine.com/forums/posts/f12/t95719.html?page=1

You and Max have done a great job for the joomla community.

Thanks a lot!
kwok