Get Email/ Phone from Cust table

https://rahulmsdax.blogspot.com/2019/10/sql-queries-to-get-customers-and-vendor.html

Find Electronic Address of Party : 

// Get LogisticsElectronicAddress from Vendtable  and get contact details.

VendTable vendtable;
            DirPartyLocation dirPartyLocation;

            select * from vendTable
                join dirPartyLocation where vendTable.Party == dirPartyLocation.Party
                    join logisticsElectronicAddress where logisticsElectronicAddress.Location == dirPartyLocation.Location
                 && logisticsElectronicAddress.ElectronicAddressRoles == "Purchase order"
                 && logisticsElectronicAddress.type == LogisticsElectronicAddressMethodType::Phone
            && logisticsElectronicAddress.IsPrimary == NoYes::Yes
                && VendTable.AccountNum == purchPurchaseOrderHeader.TRI_VendorAccount;
         
            //contactname               =   logisticsElectronicAddress.Locator;
            contactname               =   logisticsElectronicAddress.Locator;

========================================================================

//Get Phone no attached to address

RecId LocationRecId = LogisticsPostalAddress::findRecId(_shipmentTable.DeliveryPostalAddress).Location;
        RecId logisticselectronicAddrRecId = LogisticsLocation::findElectronicLocationByParent(LocationRecId).RecId;

        logisticsElectronicAddress  logisticsElectronicAddress;
        select firstonly logisticsElectronicAddress
            where logisticsElectronicAddress.Locator != ""
            && logisticsElectronicAddress.type == LogisticsElectronicAddressMethodType::Phone
            && logisticselectronicAddrRecId== logisticsElectronicAddress.Location
        && logisticsElectronicAddress.IsPrimary == NoYes::Yes;

        if(!logisticsElectronicAddress)
        {
            select firstonly logisticsElectronicAddress
            where logisticsElectronicAddress.Locator != ""
            && logisticsElectronicAddress.type == LogisticsElectronicAddressMethodType::Phone
            && logisticselectronicAddrRecId== logisticsElectronicAddress.Location
            && logisticsElectronicAddress.IsPrimary == NoYes::No;
        }
        packinglistTmp.ShipTo               =   _shipmentTable.displayDropOffLoc()+'\n'+logisticsElectronicAddress.Locator;


// Get phone no attached to address. not primay.

        CustTable custtable = CustTable::find(_salesTable.InvoiceAccount);
        Addressing                  address;
        DirPartyRecId               party;
        LogisticsPostalAddress      postalAddressloc;
        logisticsElectronicAddress  logisticsElectronicAddressloc;
        DirPartyLocation            dirPartyLocation;
        select * from logisticsElectronicAddressloc
            join dirPartyLocation
           where dirPartyLocation.Party    == custtable.Party &&
                 dirPartyLocation.Location == logisticsElectronicAddressloc.Location &&
            logisticsElectronicAddressloc.Type == LogisticsElectronicAddressMethodType::Phone;

        packinglistTmp.BillTo               =  custtable.address()+'\n'+logisticsElectronicAddressloc.Locator; 



FieldId primaryFieldId = DirPartyTable::electronicAddressType2primaryFieldId(LogisticsElectronicAddressMethodType::Email);

//Get Primary

select firstonly logisticsElectronicAddress
join dirPartyTable
where logisticsElectronicAddress.Locator != ”
&& logisticsElectronicAddress.type == LogisticsElectronicAddressMethodType::Email
&& dirPartyTable.(primaryFieldId) == logisticsElectronicAddress.RecId
&& dirPartyTable.RecId == custTable.Party;

salesTable.Email = logisticsElectronicAddress.Locator;
if(!logisticsElectronicAddress.Locator)
{

//Get other email
select firstonly logisticsElectronicAddress
join Party,Location from dirPartyLocation
where logisticsElectronicAddress.Locator != ”
&& logisticsElectronicAddress.type == LogisticsElectronicAddressMethodType::Email
&& dirPartyLocation.Party == custTable.Party
&& logisticsElectronicAddress.Location == dirPartyLocation.Location;

salesTable.Email = logisticsElectronicAddress.Locator;
if(!salesTable.Email)
salesTable.Email = custEmail;

==============================================================
For Phone :


FieldId primaryFieldId = DirPartyTable::electronicAddressType2primaryFieldId(LogisticsElectronicAddressMethodType::Phone);

//Get Primary

select firstonly logisticsElectronicAddress
join dirPartyTable
where logisticsElectronicAddress.Locator != ”
&& logisticsElectronicAddress.type == LogisticsElectronicAddressMethodType::Phone
&& dirPartyTable.(primaryFieldId) == logisticsElectronicAddress.RecId
&& dirPartyTable.RecId == custTable.Party;

salesTable.Email = logisticsElectronicAddress.Locator

Loop through Enum Values

static void LoopOverEnum(Args _args) 
{ 
    DictEnum DEnum; 
    int      i; 
    ; 
    DEnum = new DictEnum(enumName2Id("xxx")); 
    for (i=0; i < DEnum.values(); i++) 
    { 
        print DEnum.index2Label(i); 
    } 
    pause; 
}

Hide some Enum Elements for Unbound control in form ( Combo box)

// Class Declaration of Form

public class FormRun extends ObjectRun
{
    SysFormEnumComboBox         sysFormEnumComboBox;
    Set                         enumSet;
}
// Init Method of Form
public void init()
{
    //Code to restrict only some selected Enum elements appear in Lookup
    enumSet= new Set(Types::Enum);
    enumSet.add(DKTaxType::Statebatterytax);
    enumSet.add(DKTaxType::Statetiertax);

    sysFormEnumComboBox  =      sysFormEnumComboBox::newParameters(element,element.controlId(formControlStr(DKMasterItemTaxValues, MasterItemTaxValues_DKTaxType)),enumNum(DKTaxType),enumSet);
// 2nd parameter , should be the control id of the Combo box.  This works only for unbound control. so it should not link to any of the 
     super();
}

Field & Related Field Fixed Relations

Field Fixed relation

Suppose we have three tables

1) Player Table  2) Footballer 3) Cricketer

Player Table with following fields

• PlayerName (string)
• PlayerBenefits(string)
• Player Type(Enum  contains Cricketer and Footballer)

FootBaller Table with following fields

• Category (Enum contain A,B,C)
• FootBallerCode(string)
• Salary (real)

Cricketer Table with following fields

• Category (Enum contain A,B,C)
• CricCode(string)
• Salary (real)

Here is how they all created:

Now populate some data in Footballer and Cricketer table

Scenario

Now For Player Table

If we select  ‘Player Type’ = Footballer then on Player Benefits field on the Player table ,lookup should be open showing the records of footballer and similiary with the case of cricketer,for this to achieve we can use the relation called ‘Fixed field’ to accomplish this.

So go to the Player table under realtion node and create a field fixed relation and here it is what I have done

• It says that  PlayterTable.PlayerType ==0(since Player Type(Enum  contains Cricketer which has a value in database equal ‘1’ and Footballer has ‘0’) you can verify by navigating to base enum and view its properties,
• So it mean if the PlayerTable.PlayerType == Footballer then

PlayerTable.PlayerBenefits == Footballer.FootballerCode(shows the lookup of Footballer data)

And similar is the case with Cricketer see below now

So now lets insert some data on Player table

And

Related Field Fixed

Note: below is copied from external resource

This relation restricts the records selected in the related table. Only records that meet the condition are selected.
The condition is ANDed with your relation.

Secondary table filtration can be done on the basis of relation set using related field fixed.

Example:
Primary Table:tableA(col1,col2,col3)
Secondry Table:tableB(col4,col5,col6)-col5 is an enum & can contain values 0,1,2.

tableB has 6 records as following

col4      col5     col6
1)      —           0          —
2)      —           1          —
3)      —           2          —
4)      —           1          —
5)      —           0          —
6)      —           1          —

We can set the related field fixed relation in tableA as
1==tableB.col5

It will give the lookup of 3 records of tableB i.e. 2,4 & 6th records.

Based on similar understanding I have implemented following

 So we have four records in InventoryPhoneTable

But when we click the look up,only showing two record

GeneralLedger Journal Import

class GeneralLedgerJournalImport extends RunBase {     FilePath                path;     Dialog                  dlg;     ledgerJournalTable      ledgerJournalTable;     LedgerJournalNameId     journalName;     DialogField             dialogFieldFile;     DialogField             dialogJournalName;     JournalId               journalNum;     MainAccountNum          account; }

public boolean canGoBatch() {     return true; }

public LedgerJournalTable createJournalTable(JournalNameId _nameId, Str _name = '') {     ;     journalNum = JournalTableData::newTable(ledgerJournalTable).nextJournalId();     ledgerJournalTable.JournalNum = journalNum;     ledgerJournalTable.JournalName = _nameId;     ledgerJournalTable.initFromLedgerJournalName();     if(_name)     {         ledgerJournalTable.Name = _name;     }     ledgerJournalTable.insert();     return ledgerJournalTable; }

public LedgerJournalTrans createJournalTrans(ledgerJournalTable        _ledgerJournalTable,                                        MainAccountNum           _accountNum,                                        //SelectableDataArea       _legalEntity,                                        Name                     _accountingUnit,                                        Name                     _lineOfBusiness,                                        AmountCurDebit           _debit,                                        TransDate                _postingDate,                                        LedgerJournalACType       _accountType = LedgerJournalACType::Ledger                                        ) {     #Define.Branches('Branches')     LedgerJournalTrans                  ledgerJournalTrans;     //DimensionAttribute                  dimAttribAccunit = DimensionAttribute::findByName('BusinessUnit');     //DimensionAttribute                  dimAttribLineof = DimensionAttribute::findByName('Department');     DimensionAttribute                  dimAttribAccunit = DimensionAttribute::findByName('AccountingUnit');     DimensionAttribute                  dimAttribLineof = DimensionAttribute::findByName('LineofBusiness');     NumberSeq                           numberSeq;     NumberSequenceTable                 numberSequenceTable;     LedgerJournalName                   ledgerJournalName;     DimensionAttributeValue             dimAttribValue;     DimensionAttributeValueSetStorage   store = new DimensionAttributeValueSetStorage();     //_lineOfBusiness = _accountingUnit;     ledgerJournalTrans.clear();     ledgerJournalTrans.initValue();     ledgerJournalTrans.LineNum              = LedgerJournalTrans::lastLineNum(_ledgerJournalTable.JournalNum) + 1;     ledgerJournalTrans.JournalNum           = _ledgerJournalTable.JournalNum;     ledgerJournalTrans.AccountType          = _accountType;     ledgerJournalTrans.TransDate            = _postingDate;     ledgerJournalName   = LedgerJournalName::find(_LedgerJournalTable.JournalName);     numberSequenceTable = NumberSequenceTable::find(ledgerJournalName.NumberSequenceTable);     numberSeq = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence);     ledgerJournalTrans.Voucher = numberSeq.voucher();     ledgerJournalTrans.TransactionType = LedgerTransType::GeneralJournal;     if (_accountingUnit)     {         dimAttribValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttribAccunit,_accountingUnit,false,false);         if(!dimAttribValue)         {             error(strFmt("@CIT127", _accountingUnit));             return ledgerJournalTrans;         }         store.addItem(dimAttribValue);         //ledgerJournalTrans.DefaultDimension =  store.save();     }     if (_lineOfBusiness)     {         dimAttribValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttribLineof,_lineOfBusiness,false,false);         if(!dimAttribValue)         {             error(strFmt("@CIT127", _lineOfBusiness));             return ledgerJournalTrans;         }         store.addItem(dimAttribValue);         ledgerJournalTrans.DefaultDimension =  store.save();     }     if (ledgerJournalTrans.AccountType == LedgerJournalACType::Ledger)     {         ledgerJournalTrans.LedgerDimension      = DimensionStorage::getDefaultAccountForMainAccountNum(_accountNum);         if(!ledgerJournalTrans.LedgerDimension)         {             error(strFmt("@CIT127", _accountNum));             return ledgerJournalTrans;         }         ledgerJournalTrans.LedgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(ledgerJournalTrans.LedgerDimension, store.save());     }     if (_debit > 0)     {         ledgerJournalTrans.AmountCurDebit       = Currency::amount(_debit);     }     else     {         ledgerJournalTrans.AmountCurCredit       = -Currency::amount(_debit);     }     ledgerJournalTrans.insert();     return ledgerJournalTrans; }

protected Object dialog() {     dlg =super();     dialogFieldFile = dlg.addFieldValue(this.pathType(),path);     dialogJournalName = dlg.addFieldValue(extendedTypeStr(LedgerJournalNameId), journalName, "@SYS35283");     return dlg; }

public boolean getFromDialog() {     path = dialogFieldFile.value();     journalName = dialogJournalName.value();     return super(); }

Public identifierName pathType() {     return extendedtypestr(FileNameOpen); }

public void readFile (str                             _path,                       LedgerJournalNameId             _journalName)                     //  NoYes                           _isFile) {     #Define.JournalDesc('ADP GL Import')     #Define.MainAccountColumn(2)     #Define.LegalEntityColumn(1)     #Define.AmountColumn(5)     #Define.PostingDateColumn(10)     CommaIo                         io;     container                       record;     int                             row, legalEntityLen;     FileName                        filename;     str                             mainAccountNumStr, legalEntityStr, accountingUnitStr, amount;     MainAccountNum                  accountNum;     TransDate                       postingDate;     AmountCurDebit                  debit;     //SysOperationProgress            prog;     //int                             startTime = timeNow();     Map                             balanceMap = new Map(Types::String, Types::Real);     MapEnumerator                   mapEnum;     LedgerParameters                ledgerParameters;     MainAccount                     companyRoundingAccout;     ;     filename  = _path;     setPrefix(strFmt("@SYS76498", curext(), filename));     //prog = SysOperationProgress::newGeneral('',filename,100000);     try     {         io = new CommaIo(filename, 'r');         row = 1;         ttsBegin;         ledgerJournalTable  = this.createJournalTable(_journalName, #JournalDesc);         ledgerParameters    = ledgerParameters::find();         record = io.read();         do         {             row++;             record = io.read();             if(conLen(record)>2)             {                 legalEntityStr          = conPeek(record, #LegalEntityColumn);                 mainAccountNumStr       = conPeek(record, #MainAccountColumn);                 legalEntityLen          = strLen(legalEntityStr);                 accountingUnitStr       = subStr(legalEntityStr, legalEntityLen-3,4);                 //accountingUnitStr       = subStr(legalEntityStr, legalEntityLen-2,3);                 legalEntityStr          = subStr(legalEntityStr,1,2);                 accountNum      = subStr(mainAccountNumStr,1,4);                 //accountNum      = subStr(mainAccountNumStr,1,6);                 amount          = conPeek(record, #AmountColumn);                 amount          = StrRem(amount,'$');                 amount          = StrRem(amount,'(');                 amount          = StrRem(amount,')');                 debit           = str2num(amount);                 postingDate     = conPeek(record, #PostingDateColumn);                 //this.createJournalTrans(ledgerJournalTable, accountNum, legalEntityStr, accountingUnitStr, accountingUnitStr, debit,postingDate, LedgerJournalACType::Ledger);                 this.createJournalTrans(ledgerJournalTable, accountNum, accountingUnitStr, accountingUnitStr, debit,postingDate, LedgerJournalACType::Ledger);                 if (balanceMap.exists(accountingUnitStr))                 {                     balanceMap.insert( accountingUnitStr, balanceMap.lookup(accountingUnitStr) + debit);                 }                 else                 {                     balanceMap.insert( accountingUnitStr, debit);                 }             }         }while(conLen(record)>0);         mapEnum = balanceMap.getEnumerator();         while(mapEnum.moveNext())         {             if (mapEnum.currentValue() !=0 && (mapEnum.currentValue() >= -0.15 && mapEnum.currentValue() <= 0.15))             {                 companyRoundingAccout = MainAccount::find(ledgerParameters.CompanyRoundingWrite);                 //this.createJournalTrans(ledgerJournalTable, companyRoundingAccout.MainAccountId, legalEntityStr, mapEnum.currentKey(), mapEnum.currentKey(), mapEnum.currentValue(),today(), LedgerJournalACType::Ledger);                 this.createJournalTrans(ledgerJournalTable, companyRoundingAccout.MainAccountId, mapEnum.currentKey(), mapEnum.currentKey(), mapEnum.currentValue(),today(), LedgerJournalACType::Ledger);             }             else if (mapEnum.currentValue() !=0)             {                 warning(strFmt("@CIT127",accountingUnitStr ));             }         }         ttsCommit;         io = null;     }     catch (Exception::Error)     {         io = null;         throw error("@SYS19358");     } }

public void run() {     if(dlg.run())     {         //this.readFile(path,journalName,NoYes::Yes);         this.readFile(path,journalName);     } }

public static ClassDescription description() {     return "@CIT189"; }

public static void main(Args args) {     GeneralLedgerJournalImport    generalLedgerJournalImport = new generalLedgerJournalImport();     generalLedgerJournalImport.getLast();     if (generalLedgerJournalImport.prompt())     {        generalLedgerJournalImport.run();     } }