Is there a way to join two fields in one table together.

Currently I have
JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL AS J1

I wanted to say
JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES AND CF_SUBJECTSALES IN MSCRM_PHONECALL AS J1

However, that doesn't work. So I was wondering if that's even possible in Focus or there's a way to do it.

Thanks.

quote:

Posted October 05, 2007 03:29 PM
Is there a way to join two fields in one table together.

Currently I have
JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL AS J1

If the join goes to two lines you have to have an END for each on a line by itself works best.
Also you have to label each join with an unique identifier, that is J1 for the first J2 for the second.

quote:

JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES AND CF_SUBJECTSALES IN MSCRM_PHONECALL AS J1

Yuo can try this, although, I never had a need to, nor, if WebFOCUS will allow:

JOIN ATTRIBUTEVALUE AND ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES AND CF_SUBJECTSALES IN MSCRM_PHONECALL AS J1

I would:

JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL AS J1
JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTSALES IN MSCRM_PHONECALL AS J2
-RUN

Would this be a ONE-TO-ONE JOIN for both???

You can add the ALL parameter to both of Tom's joins. You would then have a multipath and the issues concommitant with that.

GG, can you comment as to whether there are multiple rows containing the same ATTRIBUTEVALUE in the phone table?

This message has been edited. Last edited by: GinnyJakes,

Thanks for the response.

Yes there are multiple rows containing the same AttributeValue in the phone table.

Joining to two fields with a FOCUS data base is not supported.
Using relational data sources this technique may be supported (depends on the type of rdbms).
But there is a fairly easy way to get this to work:
Do a single field join. So in your case it would be

JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL AS J1

Then code your request and in the request also include this where:

WHERE SECONDVALUE EQ CF_SUBJECTSALES ;

Is this idea helpful?

Are you wanting ATTRIBUTEVALUE equal to CF_SUBJECTCLIENTSERVICES || CF_SUBJECTSALES?
If you can turn this around making MSCRM_PHONECALL the host, you can use a define based join.

Previous post may be onto to something. GGOP, can you post the parts of the two masters that contain the suffix and the join to and from fields? That would make it easier for us to help you.

Also, GamP, multi-field join for fixed format sequential files is available in 7.6. That won't help GGOP as he is on 5.3.

Here's my code. I'm just not getting the correct data. If I get CF_SUBJECTCLIENTSERVICES and CF_SUBJECTSALES join properly then it will return the correct data possibly.

TABLE FILE MSCRM_STRINGMAP
PRINT
ATTRIBUTENAME
ATTRIBUTEVALUE
VALUE
COMPUTE SUBJECT/A255 = IF VALUE EQ '-Pick a Subject-' THEN 'MISSINGSUBJECT'
ELSE IF VALUE EQ 'ACB' THEN 'ACB'
ELSE IF VALUE EQ 'Account Inquiry' THEN 'ACCOUNTINQUIRY'
ELSE IF VALUE EQ 'Dealer Rep kit Request' THEN 'DEALERREPKITREQUEST'
ELSE IF VALUE EQ 'DIP (Outgoing)' THEN 'DIPOUTGOING'
ELSE IF VALUE EQ 'DSC Full Report' THEN 'DSCFULLREPORT'
ELSE IF VALUE EQ 'DSC Inquiry' THEN 'DSCINQUIRY'
ELSE IF VALUE EQ 'DSC Refund' THEN 'DSCREFUND'
ELSE IF VALUE EQ 'Full Free Unit Report' THEN 'FULLFREEUNITREPORT'
ELSE IF VALUE EQ 'Full Trial Balance Report' THEN 'FULLTRIALBALANCEREPORT'
ELSE IF VALUE EQ 'Income Trusts' THEN 'INCOMETRUSTS'
ELSE IF VALUE EQ 'Marketing Fulfillment' THEN 'MARKETINGFULFILLMENT'
ELSE IF VALUE EQ 'Other' THEN 'OTHER'
ELSE IF VALUE CONTAINS 'PPN' THEN 'PPNSLINKNOTES'
ELSE IF VALUE EQ 'Product Inquiry' THEN 'PRODUCTINQUIRY'
ELSE IF VALUE EQ 'Statement Request' THEN 'STATEMENTREQUEST'
ELSE IF VALUE EQ 'Tax Inquiry' THEN 'TAXINQUIRY'
ELSE IF VALUE EQ 'Flow Through' THEN 'FLOWTHROUGH'
ELSE IF VALUE EQ 'Market Conditions' THEN 'MARKETCONDITIONS'
ELSE IF VALUE EQ 'Book Meeting' THEN 'BOOKMEETING'
ELSE IF VALUE EQ 'Follow-up' THEN 'FOLLOWUP'
ELSE IF VALUE EQ 'Fund Campaign' THEN 'FUNDCAMPAIGN'
ELSE IF VALUE EQ 'Introduction' THEN 'INTRODUCTION'
ELSE IF VALUE EQ 'Information Change' THEN 'INFORMATIONCHANGE'
ELSE IF VALUE EQ 'Marketing' THEN 'MARKETING'
ELSE IF VALUE EQ 'Problem Resolution' THEN 'PROBLEMRESOLUTION'
ELSE IF VALUE EQ 'RSM Project' THEN 'RSMPROJECT'
ELSE IF VALUE EQ 'Rep Complaint' THEN 'REPCOMPLAINT'
ELSE IF VALUE EQ 'Rep Request/Change Meeting' THEN 'REPREQUESTCHANGEMEETING'
ELSE IF VALUE EQ 'Rep Inquiry' THEN 'REPINQUIRY'
ELSE IF VALUE EQ 'Thank You' THEN 'THANKYOU'
ELSE IF VALUE EQ 'Rep Request Promos' THEN 'REPREQUESTPROMOS'
ELSE IF VALUE EQ 'Income Trusts' THEN 'INCOMETRUSTS'
ELSE IF VALUE EQ 'Prospectus' THEN 'PROSPECTUS'
ELSE IF VALUE EQ 'Due Diligence' THEN 'DUEDILIGENCE'
ELSE VALUE;
WHERE OBJECTTYPECODE = 4210 AND (ATTRIBUTENAME EQ 'cf_subjectclientservices') OR (ATTRIBUTENAME EQ 'cf_subjectsales')
BY ATTRIBUTEVALUE NOPRINT
ON TABLE HOLD AS SUBJECTS
END
-RUN

JOIN VALUE IN SUBJECTS TO ALL SUBJECT IN MSCRM_PHONECALL AS J1
-*JOIN CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL TO ALL ATTRIBUTEVALUE IN SUBJECTS AS J1
-*JOIN CF_SUBJECTSALES IN MSCRM_PHONECALL TO ALL ATTRIBUTEVALUE IN SUBJECTS AS J2
-*JOIN ATTRIBUTEVALUE AND ATTRIBUTEVALUE IN SUBJECTS TO ALL CF_SUBJECTCLIENTSERVICES AND CF_SUBJECTSALES IN MSCRM_PHONECALL AS J1

TABLE FILE SUBJECTS
SUM
CNT.SUBJECT AS 'Subjects'
MAX.CREATEDBYNAME
OWNERID
CREATEDBY
REGARDINGOBJECTID
REGARDINGOBJECTTYPECODE
&WHEREDATE1
BY CREATEDBY
BY SUBJECT
ON TABLE HOLD AS COUNTSUB
END
-RUN

quote:

PBrightwell

Yes I want ATTRIBUTEVALUE equal to CF_SUBJECTCLIENTSERVICES || CF_SUBJECTSALES

My issue is duplicates. My results return this:

ATTRIBUTENAME ATTRIBUTEVALUE VALUE SUBJECT
cf_subjectclientservices 1 -Pick a Subject- MISSINGSUBJECT
cf_subjectsales 1 -Pick a Subject- MISSINGSUBJECT
cf_subjectsales 2 Book Meeting BOOKMEETING
cf_subjectsales 3 Follow-up FOLLOWUP
cf_subjectsales 4 Fund Campaign FUNDCAMPAIGN
cf_subjectsales 5 Introduction INTRODUCTION
cf_subjectsales 6 Information Change INFORMATIONCHANGE
cf_subjectsales 7 Marketing MARKETING
cf_subjectsales 8 Problem Resolution PROBLEMRESOLUTION
cf_subjectsales 9 RSM Project RSMPROJECT
cf_subjectsales 10 Rep Complaint REPCOMPLAINT
cf_subjectsales 11 Rep Request/Change Meeting REPREQUESTCHANGEMEETING
cf_subjectsales 12 Rep Inquiry REPINQUIRY
cf_subjectsales 13 Thank You THANKYOU
cf_subjectsales 14 Rep Request Promos REPREQUESTPROMOS
cf_subjectsales 15 PPN’s (Link Notes) PPNSLINKNOTES
cf_subjectsales 16 Income Trusts INCOMETRUSTS
cf_subjectsales 17 Prospectus PROSPECTUS
cf_subjectclientservices 17 ACB ACB
cf_subjectclientservices 18 Account Inquiry ACCOUNTINQUIRY
cf_subjectsales 18 DSC Refund DSCREFUND
cf_subjectsales 19 Due Diligence DUEDILIGENCE
cf_subjectclientservices 19 Dealer Rep kit Request DEALERREPKITREQUEST
cf_subjectclientservices 20 DIP (Outgoing) DIPOUTGOING
cf_subjectsales 20 Flow Through FLOWTHROUGH
cf_subjectsales 21 Market Conditions MARKETCONDITIONS
cf_subjectclientservices 21 DSC Full Report DSCFULLREPORT
cf_subjectclientservices 22 DSC Inquiry DSCINQUIRY
cf_subjectclientservices 23 DSC Refund DSCREFUND
cf_subjectclientservices 24 Full Free Unit Report FULLFREEUNITREPORT
cf_subjectclientservices 25 Full Trial Balance Report FULLTRIALBALANCEREPORT
cf_subjectclientservices 26 Income Trusts INCOMETRUSTS
cf_subjectclientservices 27 Marketing Fulfillment MARKETINGFULFILLMENT
cf_subjectclientservices 28 Other OTHER
cf_subjectclientservices 29 PPN's (Link Notes) PPNSLINKNOTES
cf_subjectclientservices 30 Product Inquiry PRODUCTINQUIRY
cf_subjectclientservices 31 Statement Request STATEMENTREQUEST
cf_subjectclientservices 33 Tax Inquiry TAXINQUIRY
cf_subjectclientservices 34 Flow Through FLOWTHROUGH
cf_subjectclientservices 35 Market Conditions MARKETCONDITIONS

In some cases the AttributeValues are the same and in some cases the Value is the same.

It has been a long time since I have done this and you might want to refer to the Creating Reports with WebFocus Manual (Chapter 14). The second question is can you make MSCRM_PHONECALL your host file?

If you can, your join will work like this:
JOIN SERVICE_SALES WITH CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL TAG P
TO ALL ATTRIBUTEVALUE IN SUBJECTS TAG S AS J1
END
-RUN
DEFINE FILE MSCRM_PHONECALL
SERVICE_SALES/AXXX=
P.CF_SUBJECTCLIENTSERVICES || P.CF_SUBJECTSALES;
END
TABLE FILE MSCRM_PHONECALL
etc.

Your TAGs then define which file the data is coming from. If you are joining to other files the tags remain the same.
If you can't change the hostfile, try to substring ATTRIBUTEVALUE into 2 fields. They have to be the first fields in your define. I'm not sure this will work.

JOIN SERVICE WITH ATTRIBUTEVALUE AND SALES WITH ATTRIBUTEVALUE IN SUBJECTS TAG S TO
CF_SUBJECTCLIENTSERVICES AND CF_SUBJECTSALES IN MSCRM_PHONECALL TAG P
END
-RUN
DEFINE FILE SUBJECTS
SERVICE/AXXX=SUBSTR(inlength, ATTRIBUTEVALUE, start, end, sublength, outfield);
SALES/AXXX=SUBSTR(inlength, ATTRIBUTEVALUE, start, end, sublength, outfield);
END
TABLE FILE SUBJECTS


etc

GOOD LUCK!

Or you can create a held FOCUS file from the phone file with a concatenated index and join to it.

Cribbing some of PBrightwell's code:

DEFINE FILE MSCRM_PHONECALL
SERV_SALES/AXXX=
P.CF_SUBJECTCLIENTSERVICES || P.CF_SUBJECTSALES;
END
TABLE FILE MSCRM_PHONECALL
PRINT ...
BY SERVICE_SALES
ON TABLE HOLD AS TELEHOLD FORMAT FOCUS INDEX SERV_SALES
END

JOIN ATTRIBUTEVALUE IN SUBJECTS TO SERV_SALES IN TELEHOLD AS J1

This is a tried and true method and I hope it works for you.

GGOFAnalyst,

That MS CRM String Map table is not an easy one to join to.

Take a look in your main MRE folder, there are several existing programs that use this table.

I'm not sure which folder exactly you're talking about but I haven't found a program that joins those two tables the way I want it to.

quote:

GGOFAnalyst,

GGOFAnalyst,

I guess you can use Conditional Join.

Example:
JOIN FILE TITLES AT TITLE_ID TAG TTL TO ALL
FILE ROYSCHED AT TITLE_ID TAG ROY AS J0
WHERE TTL.TITLE_ID1 EQ ROY.TITLE1_ID;
WHERE TTL.TITLE_ID2 EQ ROY.TITLE2_ID;
END

Your MRE Enterprise Domain has several programs that access the MSCRM_STRINGMAP table.

Meanwhile, if you break this problem down to smaller pieces I may begin to understand what you want to do.

You first posting questions if we can join to two columns in the same table. We may be able to, depending on the business problem you're trying to resolve:

JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL TAG J1 AS J1
JOIN ATTRIBUTEVALUE IN SUBJECTS TO CF_SUBJECTSALES IN MSCRM_PHONECALL TAG J2 AS J2

The TAG attribute prefixes all the columns in the cross-referenced file, e.g. columns in table MSCRM_PHONECALL in the first join are prefixed with J1.column-name. This is how you would be able to join twice and access the columns with different names.

I don't know if this will help you resolve what you want to do, but I would enable SQL traces and make sure that the appropriate WHERE statements appear for each JOIN, if necessary.

We are using Microsoft CRM too and created several reports on the database.
The database is rather complex and we let the developers create several views to perform the wanted selections.
The end result was very slow so we decided to create some more efficient FOCUS tables with several index fields. That database is loaded/created every night.

The main problem IMHO is that many tables use the same fieldnames which make the building process very confusing.