How to learn advanced MIGX capabilities?

I have trying to develop some CMPs using MIGX DB, and keep hitting up against what seems to be a lack of documentation about using all its features and settings. I really hope at some point a complete user guide for MIGX and MIGX DB gets created. It’s such a wonderful tool, but I’m spend hours trying to find out how to do certain things with it. It would be great to have full details about all the fields, in all 13 of the tabs in the initial MIGX configs screen, about what they are, what they do, and how to use them.

For example, I have a custom package going that uses custom tables. Basic editing works fine; I can set up the CMPs for each of the custom tables, and pull data from joined tables to populate popup values in a second table, for instance. But, I can’t solve the following:

  1. I can’t get the DB filter to work at all, except for a plain old text field. Is that because this is a custom package, or is it because of how the listbox outputs the values?

  2. There doesn’t seem to be a filter option for a listbox that selects only single values. There is only listbox-multiple and combobox.

  3. The listbox doesn’t seem to take input values the same way as TVs, even when it is set to fetch the values from a TV, which outputs a string like “First thing==1||Second thing==2”. Only the stored value (1, 2) shows up in the list, not the labels (First thing, Second thing) like TVs do.

  4. Each MIGX setup seems to let you edit just one class of your custom package. I would really like to do something like this (Company and Staff are separate tables, linked by the company_id in the Staff table):

Is that completely impossible in MIGX? I know I could make the staff table a MIGX TV, but that would store those values in a JSON string, rather than being dynamic links to individual Staff bios. Staff could be in a different formTab, but still that would require inserting new rows in the Staff table while editing the Company table.

  1. Is there a way to have multiple tabs in a single CMP, which link to the configs for each individual table to edit them? I’d like to have this:
    Screen Shot 2021-08-03 at 3.43.12 PM
    With more tabs to the right of “Companies”, like “Staff”, “Locations”, etc.

For your 5th question: multiple tabs in a single CMP
In the configuration of the menu, field “Parameters:” add the names of additional configs with double pipes.

&configs=myfirstconfig||mysecondconfig

For your 4th question: Nested MIGXdb

You need 2 configs and the relationships in your schema have to be set correctly.

In your config for Company add a field of “Input TV type” = migxdb and “Configs” = name_of_your_staff_config

In your config for Staff in the tab “MIGXdb-Settings”, set “Join Alias” to the alias of the relationship in your schema and “Check Resource” to yes. Maybe also set “Load Grid” to auto.

Sorry, that’s much too easy! :laughing:

Yes, I have the relations set. I’ll give this a try.

OK, I’ve done that, and the grid shows up, but no related records are shown. In Companies, I have this in the Joins:

[{“alias”:“staff”,“classname”:“CompanyStaff”,“on”:“staff.company_id = CompanyCompany.id”}]

and this in the schema for the Company table:

< composite alias=“staff” class=“CompanyStaff” local=“id” foreign=“company_id” cardinality=“many” owner=“local” />

I don’t think you have to fill in anything in the Company config in the field “Joins”.
It should be sufficient just to use “Join Alias” in your Staff config.

<object class="CompanyStaff" table="..." extends="xPDOSimpleObject">
    ...
    <aggregate alias="company" class="..." local="company_id" foreign="id" cardinality="one" owner="foreign" />
</object>

Yeah, I already had that in the schema, but the don’t show up.

Can you post the schema for these two tables?
Maybe also the json of the configs (right click → “Export/Import”).

Schema:

<object class="CompanyCompany" table="company_company" extends="xPDOObject">
	<field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" />
	<field key="name" dbtype="varchar" precision="100" phptype="string" null="false" />
	<field key="description" dbtype="varchar" precision="1000" phptype="string" null="true" />
	<field key="updated" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP"  extra="on update current_timestamp" />

	<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
		<column key="id" length="" collation="A" null="false" />
	</index>
	<composite alias="staff" class="CompanyStaff" local="id" foreign="company_id" cardinality="many" owner="local" />

</object>

<object class="CompanyStaff" table="company_staff" extends="xPDOObject">
	<field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" />
	<field key="first_name" dbtype="varchar" precision="100" phptype="string" null="false" />
	<field key="last_name" dbtype="varchar" precision="100" phptype="string" null="false" />
	<field key="company_type_id" dbtype="mediumint" precision="100" phptype="integer" null="false" />
	<field key="company_id" dbtype="int" precision="1000" phptype="integer" null="false" index="companyid" />
	<field key="updated" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP"  extra="on update current_timestamp" />

	<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
		<column key="id" length="" collation="A" null="false" />
	</index>
	<aggregate alias="stafftype" class="CompanyStaffType" local="staff_type_id" foreign="id" cardinality="one" owner="foreign" />
	<aggregate alias="company" class="CompanyCompany" local="company_id" foreign="id" cardinality="one" owner="foreign" />

</object>

Configs for Company. FYI, I’ve tried this both with and without the Joins.

{
  "formtabs":[
    {
      "MIGX_id":2,
      "caption":"Basic Info",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":2,
          "field":"name",
          "caption":"Company Name",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":3,
          "field":"description",
          "caption":"Description",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"textarea",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        }
      ],
      "pos":1
    },
    {
      "MIGX_id":5,
      "caption":"Staffs",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":9,
          "field":"stafflist",
          "caption":"Staffs",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"migxdb",
          "validation":"",
          "configs":"Company Staffs",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        }
      ],
      "pos":2
    }
  ],
  "contextmenus":"update||duplicate||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"update||duplicate||remove",
  "filters":"",
  "extended":{
    "migx_add":"Add Company",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"companys",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"cancel||done",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"company",
    "classname":"CompanyCompany",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":1,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"company",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":[
      {
        "alias":"staff",
        "classname":"CompanyStaff",
        "on":"staff.company_id = CompanyCompany.id"
      }
    ],
    "hooksnippets":"",
    "cmpmaincaption":"Companys",
    "cmptabcaption":"Companys",
    "cmptabdescription":"Main list of companys.",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "permissions":{
    "apiaccess":"",
    "view":"",
    "list":"",
    "save":"",
    "create":"",
    "remove":"",
    "delete":"",
    "publish":"",
    "unpublish":"",
    "viewdeleted":"",
    "viewunpublished":""
  },
  "fieldpermissions":"",
  "columns":[
    {
      "MIGX_id":1,
      "header":"ID",
      "dataIndex":"id",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
		{
      "MIGX_id":2,
      "header":"Name",
      "dataIndex":"name",
      "width":300,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    },
    {
      "MIGX_id":3,
      "header":"Description",
      "dataIndex":"description",
      "width":300,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    }
  ],
  "category":""
}

Configs for Staff:

{
  "formtabs":[
    {
      "MIGX_id":4,
      "caption":"Settings",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":5,
          "field":"text",
          "caption":"Staff info",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"textarea",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":6,
          "field":"staff_type_id",
          "caption":"Staff Type",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"@SELECT `staff_type`,`id` FROM `[[+PREFIX]]company_staff_type` ORDER BY staff_type",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||duplicate||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"update||duplicate||remove",
  "filters":[
    {
      "MIGX_id":1,
      "name":"companylist",
      "label":"Companys",
      "emptytext":"Enter a company name, or part of a name",
      "type":"textbox",
      "getlistwhere":{
        "company.name:LIKE":"%[[+companylist]]%"
      },
      "getcomboprocessor":"",
      "combotextfield":"",
      "comboidfield":"",
      "combowhere":"",
      "comboclassname":"",
      "combopackagename":"",
      "combo_use_custom_prefix":"0",
      "comboprefix":"",
      "combojoins":[
        {
          "alias":"company",
          "classname":"CompanyCompany",
          "on":"company.id = CompanyStaff.company_id"
        }
      ],
      "comboparent":"",
      "default":""
    },
    {
      "MIGX_id":2,
      "name":"clear",
      "label":"Clear",
      "emptytext":"",
      "type":"resetall",
      "getlistwhere":"",
      "getcomboprocessor":"",
      "combotextfield":"",
      "comboidfield":"",
      "combowhere":"",
      "comboclassname":"",
      "combopackagename":"",
      "combo_use_custom_prefix":"0",
      "comboprefix":"",
      "combojoins":"",
      "comboparent":"",
      "default":""
    }
  ],
  "extended":{
    "migx_add":"Add Staff",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"companystaffs",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"cancel||done",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"company",
    "classname":"CompanyStaff",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":2,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"staff",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":[
      {
        "alias":"stafftype",
        "classname":"CompanyStaffType",
        "on":"stafftype.id = CompanyStaff.staff_type_id"
      },
      {
        "alias":"company",
        "classname":"CompanyCompany",
        "on":"company.id = CompanyStaff.company_id"
      }
    ],
    "hooksnippets":"",
    "cmpmaincaption":"Company Staff",
    "cmptabcaption":"Staff",
    "cmptabdescription":"List of company staff.",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "permissions":{
    "apiaccess":"",
    "view":"",
    "list":"",
    "save":"",
    "create":"",
    "remove":"",
    "delete":"",
    "publish":"",
    "unpublish":"",
    "viewdeleted":"",
    "viewunpublished":""
  },
  "fieldpermissions":"",
  "columns":[
    {
      "MIGX_id":1,
      "header":"ID",
      "dataIndex":"id",
      "width":60,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":2,
      "header":"Staff",
      "dataIndex":"text",
      "width":400,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    },
    {
      "MIGX_id":3,
      "header":"Type",
      "dataIndex":"stafftype_staff_type",
      "width":100,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":4,
      "header":"Company",
      "dataIndex":"company_name",
      "width":200,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    }
  ],
  "category":""
}

In your Company config: Delete both the values for “Join Alias” and “Joins”.

In your Staff config: Change the value of “Join Alias” to company! (The name of the alias you would use from the viewpoint of “CompanyStaff” to query the class “CompanyCompany”.)

<object class="CompanyStaff" table="company_staff" extends="xPDOObject">
	...
	<aggregate alias="company" class="CompanyCompany" local="company_id" foreign="id" cardinality="one" owner="foreign" />
</object>

OK, I’m getting the hang of this now. Got it working!

Thanks so much.

For your other questions about Db-Filters:
Unfortunately I don’t exactly understand what you are trying to do. Maybe you can be more specific.

To select a single value, you can use “Filter Type” = combobox.
For example, if you have a config that displays all items from CompanyStaff and you want to filter them by the column “company_id”, then these settings should work:

  • filter Name: companyfilter
  • Label: Company Filter
  • Empty Text: All companies
  • Filter Type: combobox
  • getlist-where: {"company_id":"[[+companyfilter]]"}
  • getcombo processor: getcombo
  • getcombo textfield: name
  • getcombo idfield: id
  • getcombo classname: CompanyCompany

The processor reads all the rows from CompanyCompany and displays the “name” column in the combobox. When you select a value in the combobox, the setting from getlist-where is used to filter the rows from CompanyStaff.

If you want to have a fix list for the values in the combobox (and not to query them from the database), you could write a custom processor similar to the getyesnocombo processor.

1 Like

OK, that works. When I tried combobox before, it actually got displayed as multiple listbox. Something I had entered in one of the other fields must have caused this, so it’s not at all obvious what over-rode the selected input type (combobox). Yes, looks like I need to make a custom processor to create the input text and values.

What is “getcombo where” used for? What is “getcombo joins” used for?

With “getcombo where” you could filter the list of companies in the combobox. For example if you had a column “company_country” and only wanted to show the companies from a certain country.

“getcombo joins” could be used to join the database table company_company with additional database-tables to get the values for the combobox.

1 Like

Thanks again. You’re the best!

I’m noticing this error in the log:

[2021-08-23 19:01:44] (ERROR @ /home/rainbowt/ebnhc/core/xpdo/xpdo.class.php : 1827) No foreign key definition for parentClass: CompanyStaff using relation alias: company

Not sure why that is. The MIGX screens are still working, but yet I get this error when accessing the screen that loads the list of staff in a tab inside the company editing screen. As far as I can see, the foreign key IS set up (see the schema above).