Hello.
When it comes to defining udef in Sales with respect to lists there are 2 ways.
1. Creating a user defined field and assign a standard list to it.
2. Creating a user defined field and assign a user defined list to it.
I have problems with getting the item value from a standard list.
In the table UDefField the only reference to a standard list table is the field listTableId as far as I can see.
If I have defined a userdefined field that points to one of the standard entities (ie, associate, sale stage, project status) and get a the item value id, how can I then fetch the item value if the only lookup field I have is the listTableId ? https://docs.superoffice.com/en/database/tables/udeffield.html
I can see there are functions agent.GetListIdByListName("name") and NSMDOListItem item = agent.GetListItem("listname", itemId) but I cannot see a way to get listitem when I only have the listTableId
Alle Svar (3)
Hi Ummair,
There are a couple variations, but generally use the MDOAgent to get all lists, and then from that get all list items or one particular list item. Something like this should get you started.
NSMDOAgent mdoAgent;
Integer listId = 2;
Integer listItemId = 5;
NSMDOListItem GetMDOListByListId(Integer listId)
{
NSMDOListItem[] lists = mdoAgent.GetList("lists", true, "", false);
foreach(NSMDOListItem item in lists)
{
if(item.GetId() == listId)
{
return item;
}
}
throw "List Not Found";
}
NSMDOListItem[] GetMDOListItemsByListId(Integer listId)
{
NSMDOListItem list = GetMDOListByListId(listId);
String listName = list.GetType();
return mdoAgent.GetList(listName, true, "", false);
}
NSMDOListItem GetMDOListItemById(Integer listId, Integer listItemId)
{
NSMDOListItem list = GetMDOListByListId(listId);
String listName = list.GetType();
NSMDOListItem item = mdoAgent.GetListItem(listName, listItemId);
return item;
}
NSMDOListItem list = GetMDOListByListId(listId);
printLine("Found list: " + list.GetId().toString() + ": " + list.GetType());
//---------------------------------------------------------------------------------------------------------------
NSMDOListItem itemItem = GetMDOListItemById(list.GetId(), listItemId);
printLine("Found item: " + itemItem.GetId().toString() + ": " + itemItem.GetName());
printLine("---------------------------");
NSMDOListItem[] listItems = GetMDOListItemsByListId(list.GetId());
foreach(NSMDOListItem item in listItems)
printLine("Found list item: " + item.GetId().toString() + ": " + item.GetName());
printLine("done");Hope this helps!
Hey Tony.
Thank you for replying to this.
This was helpful, but not getting the desired result.
If I first fetch the listTableId by using SearchEngine which will give me list fields
SearchEngine se;
se.addFields("UDefField",
"version,lastVersionId,updated,UDefField_Id,ownerTable_id,tabOrder,fieldType,listTableId,UDListDefinition_id,UDListDefinition_id.name," +
"columnId,fieldLabel," +
"version,udefIdentity,progId"
);
se.addCriteria("UDefField.fieldType", "equals", "7");
se.addCriteria("UDefField.ownerTable_id", "equals", "7");
se.addCriteria("UDefField.version", "equals", "9");
printLine(parseJSON(se.executeJSON()).toJSON(2));The output will be:
[
{
"UDefField.version": "9",
"UDefField.lastVersionId": "37",
"UDefField.updated": "",
"UDefField.UDefField_Id": "46",
"UDefField.ownerTable_id": "7",
"UDefField.tabOrder": "7",
"UDefField.fieldType": "7",
"UDefField.listTableId": "61",
"UDefField.UDListDefinition_id": "0",
"UDefField.UDListDefinition_id.name": "",
"UDefField.columnId": "8964",
"UDefField.fieldLabel": "Label - Liste mot standard entitet",
"UDefField.version": "9",
"UDefField.udefIdentity": "7",
"UDefField.progId": "SuperOffice:7"
},
{
"UDefField.version": "9",
"UDefField.lastVersionId": "38",
"UDefField.updated": "",
"UDefField.UDefField_Id": "47",
"UDefField.ownerTable_id": "7",
"UDefField.tabOrder": "8",
"UDefField.fieldType": "7",
"UDefField.listTableId": "136",
"UDefField.UDListDefinition_id": "106",
"UDefField.UDListDefinition_id.name": "Egendefinert liste",
"UDefField.columnId": "8965",
"UDefField.fieldLabel": "Label - Liste mot egendefinert liste",
"UDefField.version": "9",
"UDefField.udefIdentity": "8",
"UDefField.progId": "SuperOffice:8"
}
]The first udef (SuperOffice:7) points to the Business list which by using MDOAgent gives me the probability list.
Hi Ummair,
OK, Here are three ways to get list name from ListTableId:
1: Hardcode a Map based on known table Ids (documented in the Database Reference)
Map listTableMap;
listTableMap.insert("219","AcademicTitle");
listTableMap.insert("225","AmountClass");
listTableMap.insert("2","Associate");
listTableMap.insert("61","Business");
listTableMap.insert("64","Category");
listTableMap.insert("70","CompanyInterest");
listTableMap.insert("109","Competitor");
listTableMap.insert("73","ContactInterest");
listTableMap.insert("19","Country");
listTableMap.insert("97","Credited");
listTableMap.insert("112","Currency");
listTableMap.insert("91","Function"); // Proj. Member Type
listTableMap.insert("216","Intent");
listTableMap.insert("94","MrMrs");
listTableMap.insert("76","Position");
listTableMap.insert("79","Priority");
listTableMap.insert("82","Rating");
listTableMap.insert("103","Reason");
listTableMap.insert("222","Rejection");
listTableMap.insert("48","Relation");
listTableMap.insert("100","Source");
listTableMap.insert("88","Status");
listTableMap.insert("130","Template");
listTableMap.insert("67","TypeFollowUp");
listTableMap.insert("85","TypeProject");
listTableMap.insert("106","TypeSelection");
listTableMap.insert("136","UserDefined");
listTableMap.insert("59","UserGroup");
String tableName = listTableMap.get("2");
printLine("Table Name: " + tableName); // prints: Associate2: Dynamically lookup the table name from the sequence table
String GetTableName(Integer tableNumber)
{
String [] searchFields = String("sequence.TableName").split(",");
NSArchiveRestrictionInfo[] restrictions;
NSArchiveRestrictionInfo res1;
res1.SetName("sequence.TableNumber");
res1.SetOperator("=");
res1.SetValues(String(tableNumber.toString()).split(","));
restrictions.pushBack(res1);
NSArchiveOrderByInfo[] order;
String[] entities = String("").split(",");
Integer pageSize = 250;
NSArchiveAgent archiveAgent;
NSArchiveListItem[] rows = archiveAgent.GetArchiveListByColumns("Dynamic", searchFields, order, restrictions, entities, 0, pageSize);
foreach (NSArchiveListItem row in rows) {
Map rowData = row.GetColumnData();
String tableName = rowData.get(searchFields[0]);
return tableName;
}
}
String table = GetTableName(2);
printLine("Table Name: " + table); // prints: ASSOCIATE3: Use the UserDefinedFieldAgent to look up the list name:
NSUserDefinedFieldInfoAgent udefAgent;
NSUserDefinedFieldInfo udefInfoList = udefAgent.GetUserDefinedFieldFromProgId("SuperOffice:7", 1); // 1: Contact, 2: Person, 3: Project, 4: Sale, 5: Temp, 6: Appointment, 7: Document
printLine("List Name: " + udefInfoList.GetMdoListName()); // prints: List Name: businessHope this helps!