one to many join with array of text/input fields

Gerald.Rager

Hello everyone

I have 3 Tables

Table 1 Paket: Fields ID;Name

Table 2 Devices: Fields ID;Name

Table 3 LinkTablePaket_Devices: Fields ID;PaketID;DevicesID;Count

i want to insert a new Paket in Table 1 and in Table 3 the references with a count

The new_paket windows show be

New Paket

     Name:       | InputField|

     Options:

     Device1:    |InputField -> Count of Table 3|
     Device2:    |InputField -> Count of Table 3|
     Device3:    |InputField -> Count of Table 3|
     Device4:    |InputField -> Count of Table 3|
     Device5:    |InputField -> Count of Table 3|
     Device6:    |InputField -> Count of Table 3|

I hope you can help me

allan

Hi,

Sounds like you want to do a join to insert into both tables. Join documentation is available int he manual if you are using the pre-built PHP or .NET libraries.

I would suggest getting everything setup to minute the count initially, and then add that once everything else is working.

The count can be done using the Field->setValue() method. You would need to query the DB to to get the correct count and then use that as the set value.

The other option is to use a trigger in the database that will automatically populate the count field. For how to do that, refer to the documentation of whatever database you are using.

Allan

Gerald.Rager

Thank you Allan for comment

maybe my englisch is very bad so you did not understand me really

the count field is a field that the user have to insert a number (i count nothing)

with the one-to-many join example

everthing works i create a Paket and this is inserted in the Table 1
but in this example i can only set the field type off the Devices Array as Checkbox
(if i set it as checkbox the checked deviceses are insertet in Table 3 (Link Table)

but i have a additional Field in the Link Table where users have to insert numbers(0-255)

i hope you understand me

Thank you

allan

Sorry for the misunderstanding.

So you want to write multiple values to the "many" table (i.e. the count)? I don't think that is something that the Editor libraries would support at the moment - I will need to experiment and get back to you.

Allan

Gerald.Rager

No Problem Allan

my fault :)

yes i have to write the PaketID the DeviceID and a Number(this is my count field) to the many table(link table)

thank you allan

Gerald.Rager

Hello Allan,

sorry to bug you but did you have any solution to get this working?

allan

Apologies, I've been delayed looking into this!

Could you describe the form that you are using for me? I'm unsure how the users will be selecting multiple values for the -many join table? Do you have a single input field that has multiple values, or is the sequence number defined by the order they selection options, or something else?

Likewise how are you displaying this information in the DataTable?

Thanks,
Allan

Gerald.Rager

Hi allan,

i made a picture is there a way that i can send you this that you know what i mean?

allan

Sorry for the delay in replying - I've just replied to your PM.

Allan

dianeinflorida

I was wondering about this also.

allan

I've just received a picture of what @Gerald is looking for - if I understand correctly (please correct me if I am wrong!), you want the ability to select multiple options from a field (D[1-3]) and then, for each of the selected options, enter a text value? Is that correct? Or will there always be a set number of options?

Either way, I think it probably is possible, but it would require a custom field type to be created that handles array based information in this manner. Currently only the checkbox field type will use arrays.

Allan

Gerald.Rager

Hello Allan,

yes for each of the selected options i want to set a text value (or a number)

okay i try to code my own custom field type for the editor form

but how i select this field in php

here my current code

Editor::inst( $db, 'LicencePaket' )
->fields(
    Field::inst( 'ID' ),
    Field::inst( 'Bezeichnung' )
)
->join(
    Join::inst( 'LicenceDevices', 'array' )
        ->join(
            array( 'ID', 'PaketID' ),
            array( 'ID', 'DeviceID' ),
            'Licence_Pak_Dev_Ref'
        )
        ->fields(
            Field::inst( 'ID' )
                ->options('LicenceDevices','ID','Name'),
            Field::inst( 'Name' )
        )
)
->process( $_POST )
->json();

allan

Hi Gerald,

The Join->fields() method describes the fields that will be read. So if you wanted, for example a field called Num included, you would use:

        ->fields(
            Field::inst( 'ID' )
                ->options('LicenceDevices','ID','Name'),
            Field::inst( 'Name' ),
            Field::inst( 'Num' )
        )

That information will then be included in the LicenceDevices array:

"LicenseDevices": [
  { "ID":  1, "Name": "Gerald", "Num": 2 },
  { "ID":  1, "Name": "Allan", "Num": 3 },
  ...
]

As many fields as you wish can be included in that manner - the part that will need some consideration is how you submit fields with multiple values. Currently Editor's built in field types only support a single property to be written.

This is most certainly something that I think warrants further exploration for inclusion in Editor core, or perhaps more likely as a plug-in.

Regards,
Allan

Gerald.Rager

Hi Allan,

i know how i select an normal field but my "num" field is not in the LicenceDevices Table
it is in the Link Table 'Licence_Pak_Dev_Ref'

thats my problem

Regards,
Gerald

Gerald.Rager

Hi Allan,

i take a little bit of time to create this again to show you want i want to do

below my three Tables:

 Table „LicencePaket“
 -------------------------------
 COL_PaketID | COL_PaketName
 1           |    Paket1
 2           |    Paket2
 3           |    Paket3

 Table „LicenceDevices“
 -------------------------------
 COL_DeviceID | COL_DeviceName
 1           |    Device1
 2           |    Device2
 3           |    Device3

 Table „Licence_Pak_Dev_Ref“
 -------------------------------
 COL_PaketID| COL_DeviceID | COL_Quantity
 1           |    1    |   255
 1           |    2    |   200
 1           |    3    |   150

They Editor Create Form should looks like

  Name:       | InputField -> COL_PaketName of Table 1|

  Options:

  Device1:    |InputField -> COL_Quantity of Table 3|
  Device2:    |InputField -> COL_Quantity of Table 3|
  Device3:    |InputField -> COL_Quantity of Table 3|

PHP("Normal One-to-many-join"):

 Editor::inst( $db, 'LicencePaket' )
     ->fields(
         Field::inst( 'COL_PaketID ' ),
         Field::inst( 'COL_PaketName' )
     )
     ->join(
         Join::inst( 'LicenceDevices', 'array' )
             ->join(
                 array( 'COL_PaketID ', 'COL_PaketID ' ),
                 array( 'COL_DeviceID ', 'COL_DeviceID ' ),
                 'Licence_Pak_Dev_Ref'
             )
             ->fields(
                 Field::inst( 'COL_DeviceID ' )
                     ->options('LicenceDevices','COL_DeviceID ','COL_DeviceName'),
                 Field::inst( 'COL_DeviceName' )
             )
     )
     ->process( $_POST )
     ->json();

this php work to create the data in the link table (with normal checkbox)
but for mine it should a text field for userinput no checkbox needed by creating all options "Devices" should be inserted with the value of the text field

I hope everyone understand me and anyone have a solution for me its very necessary for me

Regards,
Gerald