Recently we saw a huge growth on our database overnight and started to get curious what caused it. By using the query below we were table to track it to a specific table thus tracking it to a specific feature within AX. The following query will show you the largest tables in your database for AX which can then help you track down what might need to do be done.
*Note: this should be ran under a query within your data-side database for AX.
SELECT
tables.NAME AS TableName,
indexes.name as IndexName,
sum(partitions.rows) as NumberOfRows,
sum(allocation_units.total_pages) as TotalPages,
sum(allocation_units.used_pages) as UsedPages,
sum(allocation_units.data_pages) as DataPages,
(sum(allocation_units.total_pages) * 8) / 1024 as TotalSizeMB,
(sum(allocation_units.used_pages) * 8) / 1024 as UsedSizeMB,
(sum(allocation_units.data_pages) * 8) / 1024 as DataSizeMB
FROM
sys.tables
INNER JOIN
sys.indexes ON tables.OBJECT_ID = indexes.object_id
INNER JOIN
sys.partitions ON indexes.object_id = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
INNER JOIN
sys.allocation_units ON partitions.partition_id = allocation_units.container_id
GROUP BY
tables.NAME, indexes.object_id, indexes.index_id, indexes.name
ORDER BY
TotalSizeMB DESC
Monday, August 3, 2015
Tuesday, July 21, 2015
Change Text/BackGround Color Based Off Cell Value Within a Grid
In some cases within AX you may want to treat data being
displayed on the form as a quick report and within reports sometimes it’s nice
to be able to change a cells text color or back ground color based on the cells
value wither its a stored value or display value shouldn’t matter. Below will
show you how to change a single cell based on its value or change the entire
row. Since all of this code gets executed at the same location is it almost the
same code it just has subtle differences. The examples shown will show you the
difference between changing a cell text color, cell background color and row
background color which are all based off the display field/method
InventQualityOrderTable.numberOfDueDays()
Example 1. Change Cell Value Text Color Based on Display
Field.
Step 1. Set AutoDeclaration on the field control to true (in
the example shown it’s called DifferenceDueDate)
Step 2. Go to the forms datasource and create the override
method “displayOption”
Step 3. Enter the following code
public void
displayOption(Common _record, FormRowDisplayOption _options)
{
InventQualityOrderTable qualityOrder;
str 15 numberOfDueDays;
qualityOrder = _record;
//get the number of days between due
date/complete or now/duedate
numberOfDueDays = qualityOrder.NumberOfDaysDueDate();
//only do the compare if we have a
number/due date
if(CustomMethods::isNumeric(numberOfDueDays))
{
//if the
value is bigger than 0 than it should be a miss (red)
if(str2num(numberOfDueDays) > 0)
{
_options.affectedElementsByControl(DifferenceDueDate.id());
//red
_options.textColor(WinAPI::RGB2int(255,0,0));
}
else
if(str2num(numberOfDueDays)
<= 0)
{
//if the value is less than or equal to 0 than the due date
was meet and the text should be green
_options.affectedElementsByControl(DifferenceDueDate.id());
//green
_options.textColor(WinAPI::RGB2int(0,204,0));
}
}
super(_record, _options);
}
Example 2. Change Cell Value Back Color Based on Display
Field.
Step 1. Set AutoDeclaration on the field control to true (in
the example shown it’s called DifferenceDueDate)
Step 2. Go to the forms datasource and create the override
method “displayOption”
Step 3. Enter the following code
public void
displayOption(Common _record, FormRowDisplayOption _options)
{
InventQualityOrderTable qualityOrder;
str 15 numberOfDueDays;
qualityOrder = _record;
//get the number of days between due
date/complete or now/duedate
numberOfDueDays = qualityOrder.NumberOfDaysDueDate();
//only do the compare if we have a
number/due date
if(CustomMethods::isNumeric(numberOfDueDays))
{
//if the value
is bigger than 0 than it should be a miss (red)
if(str2num(numberOfDueDays) > 0)
{
_options.affectedElementsByControl(DifferenceDueDate.id());
//red
_options.backColor(WinAPI::RGB2int(255,0,0));
}
else
if(str2num(numberOfDueDays)
<= 0)
{
//if
the value is less than or equal to 0 than the due date was meet and the text
should be green
_options.affectedElementsByControl(DifferenceDueDate.id());
//green
_options.backColor(WinAPI::RGB2int(0,204,0));
}
}
super(_record, _options);
}
Example 3. Change Cell Value Back Color Based on Display
Field.
Step 1. Go to the forms datasource and create the override
method “displayOption”
Step 2. Enter the following code
public void
displayOption(Common _record, FormRowDisplayOption _options)
{
InventQualityOrderTable qualityOrder;
str 15 numberOfDueDays;
qualityOrder = _record;
//get the number of days between due
date/complete or now/duedate
numberOfDueDays = qualityOrder.NumberOfDaysDueDate();
//only do the compare if we have a
number/due date
if(CustomMethods::isNumeric(numberOfDueDays))
{
//if the value
is bigger than 0 then it should be a miss (red)
if(str2num(numberOfDueDays) > 0)
{
//red
_options.backColor(WinAPI::RGB2int(255,0,0));
}
else
if(str2num(numberOfDueDays)
<= 0)
{
//if
the value is less than or equal to 0 than the due date was meet and the text
should be green
//green
_options.backColor(WinAPI::RGB2int(0,204,0));
}
}
super(_record, _options);
}
Conclusion: The difference between setting an entire rows
color and setting a cell/field value is only different by the method _options.affectedElementsByControl(<field
control>.id()); which limits what fields the next color change affects.
The only difference
between setting a text color and a back ground color is the property textColor
vs backColor()
Friday, June 26, 2015
C# - AX AIF webservice apply company filer/field filter to existing AOT query or create query on the fly within c#
Per the past previous posts of showing how to create a query within AX and calling it via the AIF in C# I ran into the dilemma of needing to add a company filter to said defined query on the fly within the C# application.
You can do this multiple ways. You can also use the examples on how to apply any filter to a query on the fly. If you are not applying filters than the static query example(s) shown in the previous post would be the way to go.
1. Create the query on the fly via code. Apply filter via code.
Pros: complete control over the structure of the query, no need for AOT object, good for small querys with only a couple tables/fields, only 1 webservice connection is needed
Cons: lots and lots of code in order to do this. The more complex the query the more code that is needed to do this, have to define every field you want to add unless you want all fields
Services needed: QueryService: http://<AOSServer>:8101/DynamicsAx/Services/QueryService
Code Example:
string axCompanyName = "<company name within AX/dataAreaId>";
DataSet dataSet;
AXQueryService.QueryMetadata query = new AXQueryService.QueryMetadata();
AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
AXQueryService.Paging paging = null;
query.DataSources = new AXQueryService.QueryDataSourceMetadata[2];
query.Name = "AXCustomerInfo";
//set up the main customer table to pull the id from
AXQueryService.QueryDataSourceMetadata custTableDS = new AXQueryService.QueryDataSourceMetadata();
custTableDS.Name = "CustTable";
custTableDS.Table = "CustTable";
custTableDS.Enabled = true;
//define the current company to search by within AX
custTableDS.Company = axCompanyName;
//define the main datasource
query.DataSources[0] = custTableDS;
//define the field listing so we dont pull in all fields
custTableDS.DynamicFieldList = false;
custTableDS.Fields = new AXQueryService.QueryDataFieldMetadata[1];
AXQueryService.QueryDataFieldMetadata accountNum;
accountNum = new AXQueryService.QueryDataFieldMetadata();
accountNum.FieldName = "AccountNum";
accountNum.SelectionField = AXQueryService.SelectionField.Database;
custTableDS.Fields[0] = accountNum;
//let the query know we need to join the table
custTableDS.HasRelations = true;
custTableDS.JoinMode = AXQueryService.JoinMode.InnerJoin;
//add the dirtparty table to the query so we can pull in the name
AXQueryService.QueryDataSourceMetadata dirPartyTableDS = new AXQueryService.QueryDataSourceMetadata();
dirPartyTableDS.Name = "DirPartyTable";
dirPartyTableDS.Table = "DirPartyTable";
dirPartyTableDS.Enabled = true;
//define the current company to search by within AX
dirPartyTableDS.Company = axCompanyName;
//define the sub datasource
query.DataSources[1] = dirPartyTableDS;
//define the field listing so we dont pull in all the fields
dirPartyTableDS.DynamicFieldList = false;
dirPartyTableDS.Fields = new AXQueryService.QueryDataFieldMetadata[1];
AXQueryService.QueryDataFieldMetadata name;
name = new AXQueryService.QueryDataFieldMetadata();
name.FieldName = "Name";
name.SelectionField = AXQueryService.SelectionField.Database;
dirPartyTableDS.Fields[0] = name;
dirPartyTableDS.HasRelations = false;
//execute the new dynamic query
dataSet = client.ExecuteQuery(query, ref paging);
2. Create AOT query object. Read in properties(meta data) from the AOT object, apply filter and run query.
Pros: complex queries can be defined and managed within the AOT, less code is needed
Cons: You need to use 2 services instead of 1 (Query & MetaData Services, both are built into default AX)
Services needed: QueryService: http://<AOSServer>:8101/DynamicsAx/Services/QueryService / Metadata Service: http://<AOSServer>:8101/DynamicsAx/Services/MetadataService
Code Example:
string axCompanyName = "<company name within AX/dataAreaId>";
DataSet dataSet;
AXMetadataService.AxMetadataServiceClient metaClient = new AXMetadataService.AxMetadataServiceClient();
AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
AXMetadataService.QueryMetadata metaQuery = metaClient.GetQueryMetadataByName(new string[] { "<aot query object name>" })[0];
AXQueryService.QueryMetadata query = new AXQueryService.QueryMetadata();
AXQueryService.Paging paging = null;
//convert the metadataservice metadata to the queryservice metadata so we can execute the query with the properties/metadata found on the aot query object
using (MemoryStream memoryStream = new MemoryStream())
{
DataContractSerializer metaSerializer = new DataContractSerializer(typeof(AXMetadataService.QueryMetadata));
DataContractSerializer querySerializer = new DataContractSerializer(typeof(AXQueryService.QueryMetadata));
metaSerializer.WriteObject(memoryStream, metaQuery);
memoryStream.Seek(0, SeekOrigin.Begin);
query = (AXQueryService.QueryMetadata)querySerializer.ReadObject(memoryStream);
}
//define the company filter based on what the user has selected
query.DataSources[0].Company = axCompanyName;
query.DataSources[0].DataSources[0].Company = axCompanyName;
//execute the query and get the results
dataSet = client.ExecuteQuery(query, ref paging);
As you can see both examples return the same results however the 2nd example is far less code. The example were using only has 2 fields that are being returned but on a more complex query that may contain 5-10 tables and more fields its pretty easy to tell that the easy way to go is to define the query within the AOT and get the properties , apply filter, execute query (2nd example)
You can also apply a filter to any range/field via the following method (this would work on both examples)
AXQueryService.QueryDataRangeMetadata range = new AXQueryService.QueryDataRangeMetadata()
{
Enabled = true,
FieldName = "dataAreaId",
Value = axCompanyName
};
query.DataSources[0].Ranges = new AXQueryService.QueryRangeMetadata[] { range };
You can do this multiple ways. You can also use the examples on how to apply any filter to a query on the fly. If you are not applying filters than the static query example(s) shown in the previous post would be the way to go.
1. Create the query on the fly via code. Apply filter via code.
Pros: complete control over the structure of the query, no need for AOT object, good for small querys with only a couple tables/fields, only 1 webservice connection is needed
Cons: lots and lots of code in order to do this. The more complex the query the more code that is needed to do this, have to define every field you want to add unless you want all fields
Services needed: QueryService: http://<AOSServer>:8101/DynamicsAx/Services/QueryService
Code Example:
string axCompanyName = "<company name within AX/dataAreaId>";
DataSet dataSet;
AXQueryService.QueryMetadata query = new AXQueryService.QueryMetadata();
AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
AXQueryService.Paging paging = null;
query.DataSources = new AXQueryService.QueryDataSourceMetadata[2];
query.Name = "AXCustomerInfo";
//set up the main customer table to pull the id from
AXQueryService.QueryDataSourceMetadata custTableDS = new AXQueryService.QueryDataSourceMetadata();
custTableDS.Name = "CustTable";
custTableDS.Table = "CustTable";
custTableDS.Enabled = true;
//define the current company to search by within AX
custTableDS.Company = axCompanyName;
//define the main datasource
query.DataSources[0] = custTableDS;
//define the field listing so we dont pull in all fields
custTableDS.DynamicFieldList = false;
custTableDS.Fields = new AXQueryService.QueryDataFieldMetadata[1];
AXQueryService.QueryDataFieldMetadata accountNum;
accountNum = new AXQueryService.QueryDataFieldMetadata();
accountNum.FieldName = "AccountNum";
accountNum.SelectionField = AXQueryService.SelectionField.Database;
custTableDS.Fields[0] = accountNum;
//let the query know we need to join the table
custTableDS.HasRelations = true;
custTableDS.JoinMode = AXQueryService.JoinMode.InnerJoin;
//add the dirtparty table to the query so we can pull in the name
AXQueryService.QueryDataSourceMetadata dirPartyTableDS = new AXQueryService.QueryDataSourceMetadata();
dirPartyTableDS.Name = "DirPartyTable";
dirPartyTableDS.Table = "DirPartyTable";
dirPartyTableDS.Enabled = true;
//define the current company to search by within AX
dirPartyTableDS.Company = axCompanyName;
//define the sub datasource
query.DataSources[1] = dirPartyTableDS;
//define the field listing so we dont pull in all the fields
dirPartyTableDS.DynamicFieldList = false;
dirPartyTableDS.Fields = new AXQueryService.QueryDataFieldMetadata[1];
AXQueryService.QueryDataFieldMetadata name;
name = new AXQueryService.QueryDataFieldMetadata();
name.FieldName = "Name";
name.SelectionField = AXQueryService.SelectionField.Database;
dirPartyTableDS.Fields[0] = name;
dirPartyTableDS.HasRelations = false;
//execute the new dynamic query
dataSet = client.ExecuteQuery(query, ref paging);
2. Create AOT query object. Read in properties(meta data) from the AOT object, apply filter and run query.
Pros: complex queries can be defined and managed within the AOT, less code is needed
Cons: You need to use 2 services instead of 1 (Query & MetaData Services, both are built into default AX)
Services needed: QueryService: http://<AOSServer>:8101/DynamicsAx/Services/QueryService / Metadata Service: http://<AOSServer>:8101/DynamicsAx/Services/MetadataService
Code Example:
string axCompanyName = "<company name within AX/dataAreaId>";
DataSet dataSet;
AXMetadataService.AxMetadataServiceClient metaClient = new AXMetadataService.AxMetadataServiceClient();
AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
AXMetadataService.QueryMetadata metaQuery = metaClient.GetQueryMetadataByName(new string[] { "<aot query object name>" })[0];
AXQueryService.QueryMetadata query = new AXQueryService.QueryMetadata();
AXQueryService.Paging paging = null;
//convert the metadataservice metadata to the queryservice metadata so we can execute the query with the properties/metadata found on the aot query object
using (MemoryStream memoryStream = new MemoryStream())
{
DataContractSerializer metaSerializer = new DataContractSerializer(typeof(AXMetadataService.QueryMetadata));
DataContractSerializer querySerializer = new DataContractSerializer(typeof(AXQueryService.QueryMetadata));
metaSerializer.WriteObject(memoryStream, metaQuery);
memoryStream.Seek(0, SeekOrigin.Begin);
query = (AXQueryService.QueryMetadata)querySerializer.ReadObject(memoryStream);
}
//define the company filter based on what the user has selected
query.DataSources[0].Company = axCompanyName;
query.DataSources[0].DataSources[0].Company = axCompanyName;
//execute the query and get the results
dataSet = client.ExecuteQuery(query, ref paging);
As you can see both examples return the same results however the 2nd example is far less code. The example were using only has 2 fields that are being returned but on a more complex query that may contain 5-10 tables and more fields its pretty easy to tell that the easy way to go is to define the query within the AOT and get the properties , apply filter, execute query (2nd example)
You can also apply a filter to any range/field via the following method (this would work on both examples)
AXQueryService.QueryDataRangeMetadata range = new AXQueryService.QueryDataRangeMetadata()
{
Enabled = true,
FieldName = "dataAreaId",
Value = axCompanyName
};
query.DataSources[0].Ranges = new AXQueryService.QueryRangeMetadata[] { range };
Tuesday, June 23, 2015
C# - Creating a custom AX AIF web service that accesses logic defined within an AX AOT Class object.
In order to support legacy applications sometimes you may need to pull in data from AX into standalone applications.
Below will show you how to pull in the customer names via a custom class defined within AX but called within C#
Step 1(in AX) Create Class called CustomerInfo.
Step 2(in AX) Add method called getCustomerNameList() It should contain the following
/// <summary>
/// Get a list of customer names
/// </summary>
/// <returns>
/// List of customer names
/// </returns>
/// <remarks>
/// Used for AIF Intergration with Escalus
/// </remarks>
[SysEntryPointAttribute(true),AifCollectionTypeAttribute('return', Types::String)]
public List getCustomerNameList()
{
CustTable custTable;
DirPartyTable dirPartyTable;
List customerNames = new List(Types::String);
//select all customers and names
while select * from custTable
join dirPartyTable
where dirPartyTable.RecId == custTable.Party
{
//save the current customer
customerNames.addEnd(dirPartyTable.Name);
}
return customerNames;
}
Step 3(in AX) Create a new service called CustomerInfo. Defined the class as 'CustomerInfo' under the properties for the service.
Then right click on the 'Operations' node on the new service and defined the method (under properties) as getCustomerNameList or whatever you called the method in step 2.
Step 4(in AX) Create a new service group called Customers and include the service CustomerInfo or whatever the service was called in step 3.
Step 5(in C#) Add the following webservice to your project http://<AOSServer>:8101/DynamicsAx/Services/Customers (Customers is the service group created in step 4)
Step 6(in C#) Add the following using statement using <C# Project Class Name>.<WebService Name> example: using AXAIFTest.Customers;
Step 7(in C#) Add the following code to your load statement or a method that can be called via load
CustomerInfoClient axClient = new CustomerInfoClient();
CallContext axContext = new CallContext();
//called the method that exists within an AX class object
string[] customerList = axClient.getCustomerNameList(axContext);
CustomerNames.DataSource = customerList;
As you can see from my previous posts of http://axcalated.blogspot.com/2015/06/c-creating-aif-webservice-to-access-ax.html and http://axcalated.blogspot.com/2014/10/accessing-ax-custtable-dirpatytable.html you can access data and/or logic from within
AX multiple ways. It really just depends on where you want to write the majority your code (C#, AX/X++, or by creating aot objects within AX) and what are the requirements for the client. Personally I would recommend writing a web service to access your data
that way it could not only be implemented within C#/.NET framework languages but any language that supports accessing web services which is really just about any language these days. The only requirement as well for accessing a web service is intranet/internet access
so it leaves a small footprint on the users system along with "planning for the future" as we all know everything is moving to the web including AX 7.
C# - Creating an AX AIF webservice to access AX CustTable & DirPatyTable (Customer Info) query
In order to support legacy applications sometimes you may need to pull in data from AX into standalone applications.
Below will show you how to pull in customer id's + names (CustTable + DirPartyTable) via a webservice from a query that is defined within AX that will allow you to accomplish the same thing as http://axcalated.blogspot.com/2014/10/accessing-ax-custtable-dirpatytable.html
but without requiring the user to install AX as its a webservice so technically you could implament this solution in any language.
Step 1(in AX) Create a query called CustomerInfo. This should contain the table CustTable and sub datasource table DirPartyTable. Only include the fields CustTable.AccountNum & DirPartyTable.Name so that the footprint is as small as can be.
Its a good idea to also add an order by of DirPartyTable.Name so the results are sorted A-Z. But you can sort by number as well.
Step 2(in C#) Add the following webservice to your project http://<AOSServer>:8101/DynamicsAx/Services/QueryService (by default the port is 8101 but if you changed it during the install you would need to change it in the url as well.
This is a default/built-in webservice provided by AX and should not require you to create anything within AX in order to access it.
Step 3 (in C#) Add the following code
//this will create a container for the customer number & name
public class Customer
{
public string AccountNum { get; set; }
public string Name { get; set; }
}
add the following to the load method or create a new method that can be called from the load method
List<Customer> axList = new List<Customer>();
DataSet dataSet;
AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
AXQueryService.Paging paging = null;
//execute a static query defined with the AX AOT
dataSet = client.ExecuteStaticQuery("CustomerInfo", ref paging);
//go thru all of the results of the query and add them to your customer object list
for (int custCounter = 0; custCounter <= dataSet.Tables[0].Rows.Count - 1; ++custCounter)
{
//get the current row information for the 2 tables
DataRow custRow = dataSet.Tables["CustTable"].Rows[custCounter];
DataRow dirPartyTableRow = dataSet.Tables["DirPartyTable.DirPartyTable"].Rows[custCounter];
//create new customer entry
Customer axCustomer = new Customer()
{
AccountNum = custRow["AccountNum"].ToString(),
Name = dirPartyTableRow["Name"].ToString()
};
//add current customer to the overall list
axList.Add(axCustomer);
}
//tie our newly created customer list to your data source
CustomerNames.DataSource = axList;
CustomerNames.DisplayMember = "Name";
CustomerNames.ValueMember = "AccountNum";
CustomerIds.DataSource = axList;
CustomerIds.DisplayMember = "AccountNum";
CustomerIds.ValueMember = "AccountNum";
That's it. You should now have 2 dropdowns being displayed on the form that are linked to each other.
Below will show you how to pull in customer id's + names (CustTable + DirPartyTable) via a webservice from a query that is defined within AX that will allow you to accomplish the same thing as http://axcalated.blogspot.com/2014/10/accessing-ax-custtable-dirpatytable.html
but without requiring the user to install AX as its a webservice so technically you could implament this solution in any language.
Step 1(in AX) Create a query called CustomerInfo. This should contain the table CustTable and sub datasource table DirPartyTable. Only include the fields CustTable.AccountNum & DirPartyTable.Name so that the footprint is as small as can be.
Its a good idea to also add an order by of DirPartyTable.Name so the results are sorted A-Z. But you can sort by number as well.
Step 2(in C#) Add the following webservice to your project http://<AOSServer>:8101/DynamicsAx/Services/QueryService (by default the port is 8101 but if you changed it during the install you would need to change it in the url as well.
This is a default/built-in webservice provided by AX and should not require you to create anything within AX in order to access it.
Step 3 (in C#) Add the following code
//this will create a container for the customer number & name
public class Customer
{
public string AccountNum { get; set; }
public string Name { get; set; }
}
add the following to the load method or create a new method that can be called from the load method
List<Customer> axList = new List<Customer>();
DataSet dataSet;
AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
AXQueryService.Paging paging = null;
//execute a static query defined with the AX AOT
dataSet = client.ExecuteStaticQuery("CustomerInfo", ref paging);
//go thru all of the results of the query and add them to your customer object list
for (int custCounter = 0; custCounter <= dataSet.Tables[0].Rows.Count - 1; ++custCounter)
{
//get the current row information for the 2 tables
DataRow custRow = dataSet.Tables["CustTable"].Rows[custCounter];
DataRow dirPartyTableRow = dataSet.Tables["DirPartyTable.DirPartyTable"].Rows[custCounter];
//create new customer entry
Customer axCustomer = new Customer()
{
AccountNum = custRow["AccountNum"].ToString(),
Name = dirPartyTableRow["Name"].ToString()
};
//add current customer to the overall list
axList.Add(axCustomer);
}
//tie our newly created customer list to your data source
CustomerNames.DataSource = axList;
CustomerNames.DisplayMember = "Name";
CustomerNames.ValueMember = "AccountNum";
CustomerIds.DataSource = axList;
CustomerIds.DisplayMember = "AccountNum";
CustomerIds.ValueMember = "AccountNum";
That's it. You should now have 2 dropdowns being displayed on the form that are linked to each other.
Monday, June 22, 2015
How to get contact's email addresses from a customer or vendor
How to get customer email addresses from all contacts associated with a customer from Company/Accounts receivable/Common/Customers/All customers/Contacts/Contact Info
Method exists on \Tables\CustTable
public container getInvoiceContacts()
{
container invoiceEmailAddresses;
DirPartyTable dirParty;
LogisticsElectronicAddress electronicAddress;
DirPartyLocation dirPartyLoc;
ContactPerson contactPerson;
//find all of the customer contact email addresses that are currently active
while select * from contactPerson
where contactPerson.CustAccount == this.AccountNum && contactPerson.Inactive == NoYes::No
join RecId from dirParty
where dirParty.RecId == contactPerson.Party
join Location, Type, SendInvoice, Locator FROM electronicAddress
EXISTS JOIN Location, Party FROM dirPartyLoc
WHERE electronicAddress.Location == dirPartyLoc.Location && dirParty.RecId==dirPartyLoc.Party
&& electronicAddress.Type == LogisticsElectronicAddressMethodType::Email
{
invoiceEmailAddresses += electronicAddress.Locator;
}
return invoiceEmailAddresses;
}
How to get vendor email addresses from all contacts associated with a vendor from Company/Accounts payable/Common/Vendors/All vendors/Contacts/Contact Info
Method exists on \Tables\VendTable
public container getRemittanceContacts()
{
container remittanceEmailAddresses;
DirPartyTable dirParty;
LogisticsElectronicAddress electronicAddress;
DirPartyLocation dirPartyLoc;
ContactPerson contactPerson;
//find all of the vendor contact email addresses that are currently active
while select * from contactPerson
where contactPerson.ContactForParty == this.Party && contactPerson.Inactive == NoYes::No
join RecId from dirParty
where dirParty.RecId == contactPerson.Party
join Location, Type, SendRemittance, Locator FROM electronicAddress
EXISTS JOIN Location, Party FROM dirPartyLoc
WHERE electronicAddress.Location == dirPartyLoc.Location && dirParty.RecId==dirPartyLoc.Party
&& electronicAddress.Type == LogisticsElectronicAddressMethodType::Email
{
remittanceEmailAddresses += electronicAddress.Locator;
}
return remittanceEmailAddresses;
}
As you can see customer & vendors operate pretty much the same except for the association of custaccount(customer) vs contactforparty(vendor) on the contactperson table.
I'm posting this since AX seems to love to use the view "DirPartyPostalAddressView" as the main listing for these contacts on the form. So it can take a little while of digging to discover the true relationship between these tables.
Method exists on \Tables\CustTable
public container getInvoiceContacts()
{
container invoiceEmailAddresses;
DirPartyTable dirParty;
LogisticsElectronicAddress electronicAddress;
DirPartyLocation dirPartyLoc;
ContactPerson contactPerson;
//find all of the customer contact email addresses that are currently active
while select * from contactPerson
where contactPerson.CustAccount == this.AccountNum && contactPerson.Inactive == NoYes::No
join RecId from dirParty
where dirParty.RecId == contactPerson.Party
join Location, Type, SendInvoice, Locator FROM electronicAddress
EXISTS JOIN Location, Party FROM dirPartyLoc
WHERE electronicAddress.Location == dirPartyLoc.Location && dirParty.RecId==dirPartyLoc.Party
&& electronicAddress.Type == LogisticsElectronicAddressMethodType::Email
{
invoiceEmailAddresses += electronicAddress.Locator;
}
return invoiceEmailAddresses;
}
How to get vendor email addresses from all contacts associated with a vendor from Company/Accounts payable/Common/Vendors/All vendors/Contacts/Contact Info
Method exists on \Tables\VendTable
public container getRemittanceContacts()
{
container remittanceEmailAddresses;
DirPartyTable dirParty;
LogisticsElectronicAddress electronicAddress;
DirPartyLocation dirPartyLoc;
ContactPerson contactPerson;
//find all of the vendor contact email addresses that are currently active
while select * from contactPerson
where contactPerson.ContactForParty == this.Party && contactPerson.Inactive == NoYes::No
join RecId from dirParty
where dirParty.RecId == contactPerson.Party
join Location, Type, SendRemittance, Locator FROM electronicAddress
EXISTS JOIN Location, Party FROM dirPartyLoc
WHERE electronicAddress.Location == dirPartyLoc.Location && dirParty.RecId==dirPartyLoc.Party
&& electronicAddress.Type == LogisticsElectronicAddressMethodType::Email
{
remittanceEmailAddresses += electronicAddress.Locator;
}
return remittanceEmailAddresses;
}
As you can see customer & vendors operate pretty much the same except for the association of custaccount(customer) vs contactforparty(vendor) on the contactperson table.
I'm posting this since AX seems to love to use the view "DirPartyPostalAddressView" as the main listing for these contacts on the form. So it can take a little while of digging to discover the true relationship between these tables.
Subscribe to:
Posts (Atom)