Custom lookup fields to allow the user to select multiple values

I created two tables that can be populated through AR and Inventory Management → Setup fields. I then created fields on the CustTable and the InventTable form’s to give the users a place to select these values for each using lookups to the custom tables previously created. Next, I modified the SalesTable form on both the header and lines to show the values that were setup on the customer and item levels.

I am familiar with using the SysLookupMultiSelectCtrl method in Dynamics AX 2012, but unfortunately, it will not work in Dynamics AX 2009. I tried setting the ReplaceOnLookup value to No on the field, but it combines all values selected into one messy string. I tried to then add str format to allow a space and a ; to separate values that I setup after the lookup logic and I keep getting syntax errors.

Example: Code1Code2
What I want: Code1; Code2;

I don’t follow your first paragraph is related to the second one. Where do you want to do multi selection? In your new fields? Does it mean that you want to store multiple values in a single field? It would conflict with the very basic rules of DB design. If you want to store multiple values related to a single record, create a related table.

I’m trying to give you as much information as I can. When I put a relation on the tables, I got errors when trying to select multiple saying they don’t exist in the other table. That is when I was able to find the one post on setting the property on the field I was using on the form and getting the values through a lookup. Yes, this will be on my new fields. Yes, I want to store multiple values in a single record.

The way I have it currently, it does show in the table with multiple values, but all bunched together. That’s when I tried to do the str fmt to add spacing and the character, but got syntax errors.

I do want to apologize and make a clarification. I moved the code from the InventTable form and put it in the CustVendExternalItem Form.

Don’t try to store multiple values in a single field. It’s a violation of the first normal form!

How would set the length of the field? How could relations validate the values? How would you use such a field for joining? How would you index and search it?

Seriously, it’s a bad idea. As I said, if you need multiple related values, use a related table with multiple records. That’s how relation databases are designed to work.

I haven’t been coding very long. Do you have any examples that I could look at? I followed the link you provided above, but I’m not sure exactly how to do that. I have always created a table and added a relation to another table so what appears in one will also be in the other. I do not know how to get it to allow multiple values as the link suggests. I taught myself and have used SQL for years, so the tables make sense in the aspect of looking at it through SQL.

I don’t think this way will work. The CustVendExternalItem table will only allow one value per customer. I will not be able to add the item, 2 or more times with the different values that I need to show.

I don’t know what you need to show, so it’s difficult to be specific.
You mentioned CustVendExternalItem table, so let’s say you want (for same reason) map multiple external items to a single sales order line. One line can have several external items and one item can obviously be used by several order lines, therefore we’re talking about many-to-many relation here. Such a relation can’t be implemented by those two tables alone; you have to create an additional mapping table (referring to unique indexes of the mapped tables).

in 2009 may not work this concept SysLookupMultiSelectCtrl using Override Lookup() u can populate the field into Lookup there is small bit of code available for select multi value with some special char.

As I stated above, this method is not available in 2009. I was able to use it in 2012.

yup that why i gave one alter way for use Override Lookup() in ax 2009

That’s what I currently have setup, but I can’t get the character to separate. I have tried using the str fmt, but I continue to get syntax errors. I was able to find one example, but it doesn’t work.

what the Example u saw can u send that link

We want to send Packing specifications by customer by item to shipping. The custom field on the customer was already there by previous programmers, and I created a custom field for the items. Management wanted it on the CustVendExternalItem form and have it populate on the sales order to be transmitted to our external system for shipping. It then changed to it needed to allow them to select multiple values. That’s where I’m stuck.

I’ve never been able to assign multiple external items to one item. I just tried it and I get an error stating that I cannot create a record in External item descriptions (CustVendExternalItem). Item number: TestItem, Customer.
The record already exists. Thank you for the suggestion of the mapping table. I will do research and attempt.

You’re stuck because you’re trying to do a completely wrong thing. If you need multiple related values, a single cell isn’t enough. What you need is inserting several records to a related table.

For a customer (or) vendor you cannot have multiple external item descriptions. Look at the unique index on that table.
If it is product master, you can have them product dimension wise. Example: For multiple sizes you can have external item defined for same customer.…/

I understand the functionality of AX in this respect. That’s why I have been trying to allow multiple values in a field on one line with the external items. This is what Martin has been telling me is incorrect. I greatly respect Martin’s views.

This is the correct for select multi option. then what you want to do after click Export button ? ? ?

This is the correct for multi select option after multi select then what kind of function you want to do ? ? ?

The way I currently have it, it pushes the values together as one value. I need to separate them. When I attempt a str fmt, it gives me syntax errors. Martin suggests using relations on the tables, which I’m trying to figure out. I’m missing something because the relations do not work for me. I’m still trying to figure it out.

This is my code for the lookup values and trying to add the character. The commented out part is what I was trying to do by mimicking the website that I provided earlier.
public void lookup()
Query query = new Query();
QueryBuildDataSource qbdsValueAdd;
sysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(IDC_ItemValueAddedService), this);

sysTableLookup.addLookupField(fieldNum(IDC_ItemValueAddedService, ItemVASCode));
sysTableLookup.addLookupField(fieldNum(IDC_ItemValueAddedService, Description));

qbdsValueAdd = query.addDataSource(tableNum(IDC_ItemValueAddedService));


// this.setSelection(strlen(this.text()),strlen(this.text()));
//idcItemValueAddedService.text(idcItemValueAddedService.valueStr() + ‘;’);