[TYPO3-english] Typo3 foreign_table: How to specify a specific column to a select drop down menu?

[christian ewigfrost]

Basically a simple question (i guess):

I want to add records in the Typo3 backend. Thatfor i made an extension containing 4 different classes. While adding a record of one specific class to a folder via the backend i want to have a select that lets me chose from items of a column from another class. I know i have to use a foreign_table for this like in this code:

'kundeuid' => array(
'exclude' => 1,
'label' => 'LLL:EXT:icingaconfgen/Resources/Private/Language/locallang_db.xlf:tx_icingaconfgen_domain_model_appliance.kundeuid',
'config' => array(
'type' => 'select',
'renderType' => 'selectSingle',
'foreign_table' => 'tx_icingaconfgen_domain_model_kunde',
'foreign_table_where' => 'ORDER BY tx_icingaconfgen_domain_model_kunde.kundeuid asc',
'items' => array(
array('-- Select Kunde --', 0),
),
'size' => 1,
'maxitems' => 1
),
),

But the problem is i can't specify that the selection should only include properties of a determined column. Instead the select drop down menu seems to use properties of the very first column. How can i specify the column of the 'kundeuid' property?

[Mikel]

Hi,

I’m not sure, which properties you want to use for your selection.

'foreign_table' => 'tx_icingaconfgen_domain_model_kunde‘,

'foreign_table_where' => 'AND tx_icingaconfgen_domain_model_kunde.your_property = your_value',

See https://docs.typo3.org/typo3cms/TCAReference/7.6/Reference/Columns/Select/#foreign-table-where <https://docs.typo3.org/typo3cms/TCAReference/7.6/Reference/Columns/Select/#foreign-table-where>

Mikel

'kundeuid' => array(
'exclude' => 1,
'label' => 'LLL:EXT:icingaconfgen/Resources/Private/Language/locallang_db.xlf:tx_icingaconfgen_domain_model_appliance.kundeuid',
'config' => array(
'type' => 'select',
'renderType' => 'selectSingle',
'foreign_table' => 'tx_icingaconfgen_domain_model_kunde',
'foreign_table_where' => 'ORDER BY tx_icingaconfgen_domain_model_kunde.kundeuid asc',
'items' => array(
array('-- Select Kunde --', 0),
),
'size' => 1,
'maxitems' => 1
),
),
But the problem is i can't specify that the selection should only include properties of a determined column. Instead the select drop down menu seems to use properties of the very first column. How can i specify the column of the 'kundeuid' property?
_______________________________________________
TYPO3-english mailing list
http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english

[christian ewigfrost]

Hmmm... But that's not waht i want to do: What this does is just filtering the assignable records by their properties and then lets me choose the record in the select. But it assigns the entire record or the uid of the record to the property of the creatable record as understand.

What i want to do is:

Say i'd set 'kundeuid' with the value 'customer01' or something while creating a record of the class Kunde. Then i create a record of the class 'Appliance'. It also has a property named 'kundeuid' and i want to set it through a select that is filled with the 'kundeuid' s from all the records of the class Kunde. One option of this select would be 'customer01' from the record of Kunde i created. I'd choose it and the property 'kundeuid' of the created Appliance record should be also 'customer01'...

[Mikel]

Do you need something like that?
'foreign_table_where' => 'AND tx_icingaconfgen_domain_model_kunde.your_property = ###REC_FIELD_[tx_icingaconfgen_your_model_to_compare.your_property]###‘,

Sorry, but I’m not sure if I understand your use case. It is a bit hard to read :-)

You can also have a look on https://docs.typo3.org/typo3cms/TCAReference/ColumnsConfig/Type/Select.html#itemsprocfunc <https://docs.typo3.org/typo3cms/TCAReference/ColumnsConfig/Type/Select.html#itemsprocfunc>
You can prepare the items for the select in your own method.

Mikel

Post by christian ewigfrost
Hmmm... But that's not waht i want to do: What this does is just filtering the assignable records by their properties and then lets me choose the record in the select. But it assigns the entire record or the uid of the record to the property of the creatable record as understand.
Say i'd set 'kundeuid' with the value 'customer01' or something while creating a record of the class Kunde. Then i create a record of the class 'Appliance'. It also has a property named 'kundeuid' and i want to set it through a select that is filled with the 'kundeuid' s from all the records of the class Kunde. One option of this select would be 'customer01' from the record of Kunde i created. I'd choose it and the property 'kundeuid' of the created Appliance record should be also 'customer01'... _______________________________________________
TYPO3-english mailing list
http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english

[christian ewigfrost]

Quote: Mikel wrote on Tue, 24 October 2017 16:40
----------------------------------------------------

Post by Mikel
Do you need something like that?
'foreign_table_where' => 'AND tx_icingaconfgen_domain_model_kunde.your_property = ###REC_FIELD_[tx_icingaconfgen_your_model_to_compare.your_property]###',
Sorry, but I'm not sure if I understand your use case. It is a bit hard to read :-)
You can also have a look on https://docs.typo3.org/typo3cms/TCAReference/ColumnsConfig/Type/Select.html#itemsprocfunc <https://docs.typo3.org/typo3cms/TCAReference/ColumnsConfig/Type/Select.html#itemsprocfunc>
You can prepare the items for the select in your own method.
Mikel

Post by christian ewigfrost
Hmmm... But that's not waht i want to do: What this does is just filtering the assignable records by their properties and then lets me choose the record in the select. But it assigns the entire record or the uid of the record to the property of the creatable record as understand.
Say i'd set 'kundeuid' with the value 'customer01' or something while creating a record of the class Kunde. Then i create a record of the class 'Appliance'. It also has a property named 'kundeuid' and i want to set it through a select that is filled with the 'kundeuid' s from all the records of the class Kunde. One option of this select would be 'customer01' from the record of Kunde i created. I'd choose it and the property 'kundeuid' of the created Appliance record should be also 'customer01'... _______________________________________________
TYPO3-english mailing list
TYPO3-english (at) lists.typo3.org
http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english

----------------------------------------------------

OK, let me try to explain again. Maybe my thought process comes across better this time:

In the backend of TYPO3 i have the folder where my records are stored of cause. There are records of the class "Kunde" stored (these are the customers - just the german word for it). Each record of this class has the property 'kundeuid' which isn't the same as the automatically generated property 'uid', but a property that was set manually. Then there are records of the class Appliance. This Appliance record has a property that is also named 'kundeuid' and acts as a way to determine which 'Kunde' record this 'Appliance' record belongs to. (technically it's a 1:n relation between Kunde and Appliance).

Now what i want to do is:

The user should be able to create a record of Appliance in the backend. The property 'kundeuid' of Appliance should be a select drop down menu that has all the 'kundeuid' values of all existing Kunde records in it. The user should choose one of those values to determine visually which Kunde record the Appliance record "belongs" to. The property 'kundeuid' of the class Appliance should get exactly this value! Let me get to the problem now...

My problem currently:

'type' => 'select',
'renderType' => 'selectSingle',
'foreign_table' => 'tx_icingaconfgen_domain_model_kunde',

The code above fills the select drop down menu with records of the class Kunde by using it as a foreign_table. That's clear to me... BUT: The property 'kundeuid' of class Appliance doesn't get the value assigned to it that i want. In the drop down menue i don't see the 'kundeuid' property values of all te Kunde records but other values of another property (i think it's the first column of that table). If i choose it in the drop down and then create the Appliance record the Appliance record gets the value of the automatically generated property 'uid' of the specific Kunde record assigned... But i want to assign my own property value 'kundeuid' to it...

Is this possible at all or do i missunderstand the entire concept of it?

[Mikel]

I think, I now understand.
You just want to have a different label in your select box, right?

If so, you can change that in the TCA of Kunde.

'ctrl' => [
'label' => 'name',

Change the value of label to ‚kundeuid'
Or add the „label_alt“ option.

But that would change the label of your records Kunde global. So your customers will also be shown with that label in the backend listing.

If you can’t change it global, you need to prepare your items by itemsProcFunc. You can pass the records to your own method and prepare them, before they are assigned to the dropdown.

BTW: In my opinion, this relation is more complicated than it should be. Do you want to group customers in that way? Or what is your target of ‚kundeuid‘?

[christian ewigfrost]

Quote: Mikel wrote on Wed, 25 October 2017 15:45
----------------------------------------------------

Post by Mikel
I think, I now understand.
You just want to have a different label in your select box, right?
If so, you can change that in the TCA of Kunde.
'ctrl' => [
'label' => 'name',
Change the value of label to ‚kundeuid'
Or add the „label_alt" option.
But that would change the label of your records Kunde global. So your customers will also be shown with that label in the backend listing.
If you can't change it global, you need to prepare your items by itemsProcFunc. You can pass the records to your own method and prepare them, before they are assigned to the dropdown.
BTW: In my opinion, this relation is more complicated than it should be. Do you want to group customers in that way? Or what is your target of ‚kundeuid'?

----------------------------------------------------

Thanks for the label tip... But actually what i want to do is soemthing else. I just find it hard to describe because it's the first time i work with TYPO3. I think the following describes it best:

I do not want to change the label field of the select items but rather change their values to a value other than the "uid" of the foreign records. That's exactly what i want to do, i want to assign the values of kundeuid to a value other than the "uid" of the foreign records. Is that possible?

[Mikel]

OK. Yes, that is possible. Like I said —> itemsProcFunc.

Go into your tca, to the select field.
'renderType' => 'selectSingle',
'foreign_table' => 'tx_cvfoobar_domain_model_kunde',
'itemsProcFunc' => ‚Foobar\CvFoobar\Tca\SelectProcFunc->prepareItems',

class SelectProcFunc
{

public function prepareItems($param) {
$newItems = [];
foreach ($param['items'] as $item) {
$newItem = [
0 => 'Enter your label',
1 => 'enter_your_value'
];
$newItems[] = $newItem;
}
$param['items'] = $newItems;
return $param;
}

}

Just as a quick example. That inserts only static values and labels.
To get more information from related Kunde record, you need to make an instance of the repository and load the record by uid.
You need to allow string inserts, if kundeuid is just a string.

BUT: This is not a very safe solution. You write the values into the database. What happens, if a user changes the kundeuid in one of the records? The relation is broken, as the value has been written into the database.
Also, for db queries, you need to compare strings. You also need to evaluate, that kundeuid is unique (if needed).

What is your target? Do you want to group records by that attribute „kundeuid“? If so, you should better add another model „Kundengruppe“ with m:n relations to Appliance and Kunde. This also would make db queries more easy, as you can build queries from both sides and get your Appliance by Kundengruppe and also your Kunden by Kundengruppe.

Mikel

[christian ewigfrost]

Quote: Mikel wrote on Wed, 25 October 2017 16:38
----------------------------------------------------

OK. Yes, that is possible. Like I said --> itemsProcFunc.
Go into your tca, to the select field.
'renderType' => 'selectSingle',
'foreign_table' => 'tx_cvfoobar_domain_model_kunde',
'itemsProcFunc' => ‚Foobar\CvFoobar\Tca\SelectProcFunc->prepareItems',
class SelectProcFunc
{
public function prepareItems($param) {
$newItems = [];
foreach ($param['items'] as $item) {
$newItem = [
0 => 'Enter your label',
1 => 'enter_your_value'
];
$newItems[] = $newItem;
}
$param['items'] = $newItems;
return $param;
}
}
Just as a quick example. That inserts only static values and labels.
To get more information from related Kunde record, you need to make an instance of the repository and load the record by uid.
You need to allow string inserts, if kundeuid is just a string.
BUT: This is not a very safe solution. You write the values into the database. What happens, if a user changes the kundeuid in one of the records? The relation is broken, as the value has been written into the database.
Also, for db queries, you need to compare strings. You also need to evaluate, that kundeuid is unique (if needed).
What is your target? Do you want to group records by that attribute „kundeuid"? If so, you should better add another model „Kundengruppe" with m:n relations to Appliance and Kunde. This also would make db queries more easy, as you can build queries from both sides and get your Appliance by Kundengruppe and also your Kunden by Kundengruppe.
Mikel

----------------------------------------------------

Actually assigning the auto generated uid property was the right thing to do, there was just a missunderstanding between my boss and me. My aim is to generate .conf files for the Icinga2 monitoring system and using the tables generated by the extension. A user should be able to create Hosts, Appliances and also create Services that are directly assigned to a Host (and also specified in the .conf files of Icinga, but that has nothing to do with TYPO3, so i won't go into detail). Then an admin should be able to check the entries the user made in the TYPO3 backend and then run a php script that creates those .conf files from the tables that the extension generated. I'm not quite sure yet how to do this yet, but as this has nothing to do with TYPO3 i'll ask at Stackoverflow.^^

But there is one thing i don't know yet: I just can't find the tables in phpmyadmin. I don't know why.