How to use Workflow Elements Query - Get a Single Value

(0) Share

Report

Posted on by Cyclefitness

I have tried everything I know to use Aiden's tool, the "Get a Single Value" component. Here's what I'm trying to do:

A payment line item record has two attributes:
1. Cost Code lookup
2. FIPS single text.
3. General Ledger lookup
The related Cost Code record has two related "General Ledger" records
The General Ledger record has two attributes:
1. ID
2. FIPS single text

I need a workflow to run on create of the payment line item that gets me the General Ledger ID where the FIPS = the FIPS on the payment line item.

I *think* AIden's https://kaskela.wordpress.com/activity-query-for-single-value/ will do this, but I cannot figure out how to build the Query.

I really appreciate any help.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

David Jennaway 14,065 on at

Like (0)

Report

I don't think this will be possible. It looks like your query needs to join two records on a field that is not part of a relationship (the FIPS single text field), and that is not possible in FetchXML (and hence not possible in a view either)

Was this reply helpful? Yes No
Aiden Kaskela 19,696 on at

Like (0)

Report
Hi Deb,

David's statement was a little off - you can indeed have links based on non-lookup fields. The performance won't be fantastic because the fields won't be indexed, but it works. You can use this Fetch to verify - link accounts and contacts based on telephone 1 (with the last name Kaskela)

<fetch count="25"> <entity name="account"> <attribute name="name" /> <link-entity name="contact" to="telephone1" from="telephone1"> <filter> <condition attribute="lastname" value="Kaskela" operator="eq" /> </filter> </link-entity> </entity> </fetch>

If I understand your scenario, you're trying to query for a GL with the FIPS number on your payment line item> Do you need to link through the Cost Code record or can you just query for the GL Account with the FIPS number? I'll build out the query for you but it'll be a lot easier if we don't need to consider the Cost Code record.

Thanks,

Aiden

Was this reply helpful? Yes No
Cyclefitness on at

Like (0)

Report

Thanks Aiden. I do need the Cost Code Record as it is the parent of the General Ledger record. Then there are two children; one for FIPS 041 and one for FIPS 570. I'm excited to see what you do with this. I assume you're using the FetchXML Builder tool?

Was this reply helpful? Yes No
Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report

Hi Deb,

The Fetch XML builder for the XRM toolbox only uses links with relationships so it'll be manual. So in that clarification, could you query for GL with FIPS = 041, or does it have to be FIPS = 041 and that specific Cost Code record?

And a followup - are you trying to set the GL lookup on the payment line item? That part isn't possible using the query. You can get back the ID of a record, but not a reference to the record as a lookup. It's a technical limitation because the return value of the workflow activity needs to explicitly say what entity is being references, and since it's query based there's no way to know ahead of time. So if you want the GL ID or another field saved on the payment that's fine, but it won't set the lookup on that record unfortunately.

Thanks,

Aiden

Was this reply helpful? Yes No
Cyclefitness on at

Like (0)

Report

[quote user="Aiden Kaskela"]

could you query for GL with FIPS = 041, or does it have to be FIPS = 041 and that specific Cost Code record?[/quote]

It has to be FIPS 041 and that specific Cost Code record. There will be one record as the result.

[quote user="Aiden Kaskela"]

And a followup - are you trying to set the GL lookup on the payment line item? That part isn't possible using the query. You can get back the ID of a record, but not a reference to the record as a lookup. It's a technical limitation because the return value of the workflow activity needs to explicitly say what entity is being references, and since it's query based there's no way to know ahead of time. So if you want the GL ID or another field saved on the payment that's fine, but it won't set the lookup on that record unfortunately.

[/quote]

Yes, that's what I'm trying to do. ;-(

Was this reply helpful? Yes No
Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report
Hi Deb,

You can use the Workflow Elements to query for the correct GL ID to use, then have a very simple workflow activity that takes the ID and saves it on the payment line item (if you don't have the development tools, I could write that for you in a couple minutes).

To get back your GL ID, query for the Cost Code record, link to the GL (and include the ID as an attribute), link to payment line item (on FIPS), then add the condition that the Line Item creates data (so the workflow filters to just that record).

Here's a rough attempt at it:

<fetch> <entity name="CostCodeName"> <link-entity name="GlEntityName" from="Gl_CostCodeLookup" to="CostCodeId" alias="gl"> <attribute name="GlEntityId" /> <link-entity name="PaymentLineItemName" from="Payment_FIPSField" to="GL_FIPSField" alias="payment"> <filter type="and"> <condition attribute="PaymentLineItemID" operator="not-null" /> </filter> </link-entity> </link-entity> </entity> </fetch>

Where

CostCodeName, GlEntityName & PaymentLineItemName are the names of the entities

CostCodeId, GlEntityId, PaymentLineItemID = The field name of the primary key for the entities

Gl_CostCodeLookup = The lookup field on the GL referencing Cost Code

Payment_FIPSField, GL_FIPSField = The FIPS field name on the payment line item and GL respectively

You can plugin this in to the workflow and you should get a single value back on the text (or ID) output of the workflow activity. To test, call the activity step and create a Task record with the output fields in the subject to make sure you're getting back what you expect. A note - make sure to delete all the line breaks in the Fetch or it will get cut off when you paste it into the workflow activity.

Thanks,

Aiden

Was this reply helpful? Yes No