Does ExportMI support selecting data from multiple tables in an individual query?

I'm attempting to prove out whether or not I can use an individual query and API call to get data returned for MITMAS and corresponding MITFAC information for each item in MITMAS that has a record in MITFAC.

Basically looking for a JOIN statement in ExportMI.

Thanks,

Jon

Find more posts tagged with

ION Connect

Accepted answers

Legacy Contributor

Greetings;

You can create a "custom" MI in CMS010 and CMS015 to be published under CMS100MI. It will let you join the two tables, but it restricts the filters to the "main" table in the Information Category. So, based on what you have as criteria you would have to set the table that you could restrict to the smallest data set and do the final filtering either client side, or perhaps in the new API Flow in ION.

Billy

All comments

Maxime Richard

Hello,

The retrieval of information is restricted to a single table in M3. Joins etc. to other tables are not possible.

Legacy Contributor

Thanks,

We need to retrieve a subset of MITMAS records that have MITMAS.MMCFI5 = 'ABC', and also MITFAC.M9FACI = '123', in one individual API call or query via API.

Can you think of any existing solution for this?

Jon

Legacy Contributor

Does ExportMI support a "like" operator, like 'ABC%'?

Legacy Contributor

We need to have "and MMITNO like '123%'" condition on our ExportMI call, is this possible?

* from MITMAS where MMCFI5 = 'Y' and MMITNO like 'S01%'

Thanks,

Jon

Maxime Richard

Hello,

As far as i know and according to NCR 9966, it's not possible. Only operator like =, <, > are usable but maybe you can use them like this :

MMITNO > 'S01' and MMITNO < 'S01ZZZZZZZZZZZZ' (15 digits)

Legacy Contributor

Greetings;

Billy

Legacy Contributor

Thanks Billy,

I have created a info category browser (CMS010) for the MITFAC table, for the related tables (CMS011) we have MITMAS and OCUSIT. I also have the custom list MI (CMS015) that uses this info category browser.

I ran the Test API tool, and I needed to supply the FACI param 3 times (T_FACI, F_FACI, and M9FACI), since MITFAC is the parent table I thought I would only need to supply M9FACI, however if I only supply this one param value, the returned list doesn't limit the result list to the facility I specified, so I supply the same param value to all 3 params and then the result list looks correct.

The result set has two columns (M9FACI, M9ITNO), how do I add more columns to the result set?

Legacy Contributor

I was able to add a new View (CRS020) to the Information Category Browser (CMS010). I added the MMCFI5 and ORPOPN fields to this View. I tried to use the Simulate List tool, however I wasn't able to select anything other than the "default" view, so I couldn't test my new custom View.

The help mentioned you need to share the view, I'm trying to figure out how to do that.

Legacy Contributor

I will use CMS010, CM011, CMS015 pages as you mentioned, this is what I need to do to get data from multiple tables in an associated manner.

Summary of what was needed to do so accomplish this:

1. Create an information browser category for the MITFAC main "parent" table (CMS010). For the Browse Program field, I chose the program name MMS003 which is the Item-Facility "parent" table.

2. Use the Related Tables page (CMS011) for this Information Browser Category. Add the related tables and modify the association logic if needed.

3. Use the View page (CRS020) for this Information Browser Category. Add the custom view, you can click the Default fields button to add the existing fields to the View, then manually add the additional fields from the "child" tables. Click the Display button to verify the fields are correct and the metadata for these fields will be updated on this page.

4. Use the Custom List MI page (CMS015) to create a custom list. This list setup page will allow you to specify the Information Browser Category, and the View.

5. Make sure to use the Related Menu on CMS015 to Update MI Repository, and also make sure the CMS010 and CMS011 page records are Activated (Status = 20).

6. Use the Test API page in M3 Metadata Publish, CMS100MI class to test the CMS015 Custom List.

antonio-caria

In MT now is possible.

Copyright © 2025 Infor. All rights reserved.