I’m banging my head against the wall trying to get a datalink to populate a field group in sort of a one to many scenario.
The datalink is simple…
“SELECT OrderID, Document, Seq FROM Invoices where Document=@Document”.
The datalink Test and the query in SQL returns 4 records but they don’t populate the Field Group properly.
The Invoices table has these values:
OrderID Document Seq
100-100 100 100
100-200 100 200
100-300 100 300
200-100 200 100
The user types in the Document Numbers in the field group but only 100 and 200 (they don’t type 100 three times). The workflow calls the datalink using the Document Number parameters “100” and “200”.
I need it to return all 4 records. It is only returning 100-100 and 200-100.
I hope someone can give me some help - all suggestions will be greatly appreciated.
in the Datalink Field Group task, you are setting the Direction to “To” in order to fill the field group, but are you setting a variable for @@Exception and then displaying that value in order to see if it includes any reason why you are not getting the other rows of data?
The other thing I can think of is that something looks right as you glance at it, but it’s not - so double check (heavily scrutinize) all the setup. My gut says that even though the Datalink works by itself, the input variable in the Datalink Field Group task is not the same when the workflow runs. Check the origin of that datalink input variable. Perhaps even SHOW it to the user before the datalink runs - like a debug window.
I have tried both To and From. When it is “To” then the field group populates with only the first parameter.
So I’ve gone down a different road.
Here is a more general question:
In my Task I want to use a datalink field group to populate a field group and display the line items on my document.
-My inputs are values in one Field Group “OrderSelection” (Order, Sequence) which the user will enter.
-I can see these line items on the document in “OrderSelection”
-My outputs are the Field Group “OrderDetails” which contains the Order, Sequence, and other details. Before the datalink, these line items are blank.
-The datalink uses SQL to pull matching details from a table.
I can’t get it to work.
Do I use from or to?
What is the Field Group to select in the datalink field group action? Is it the datalink with the inputs or where I want the output to go?
Hope this is easy to answer.
One error I had with one setup was "The workflow will not proceed because the field group(s) ‘OrderSelections’ associated with field ‘Order’ would be overwritten.
From what I understand Barb, these field groups are like a “one time use” object.
You have to write and then re-write to them. If you want to make a change to only one row in the data link action, but want to keep the others as they were, you still have to write back the other rows if you want to keep them in the field group… at least that’s my own trial and error understanding of it. Otherwise you’re left with “one row” or however many rows were returned as a result of the action you performed.
@MikeGross can you answer the FROM and TO? I literally just use whichever one does what I want when it happens and haven’t thought deeply enough about it to convey it in a simple way to anyone.
Thank-you both!
So I was on the right track but instead of my inputs being in their own line items, it is all one field group.
Input Order, Sequence and use datalink to get the rest of the fields in that field group.
Just have more testing (various scenarios) and then one hurdle left before I can start rollout.
Sometimes the Task Info is very hard to understand. We have no access to training or knowledgeable resources other than here (and I found this by accident). So it is a lot of trial and error.
@Barb , @utaylor is correct in his description, but I’m not sure on the one point he made about
so I’m going to have to test that out for myself I think it might be the multi-row processing and it sort of happens automatically. (See below)
But as for
I like to think of like this - every task has some Input and Output. Form the Task perspective (not the field group’s perspective), FROM and TO make more sense. The answer to this question drives where you enter $FieldGroup.FieldNames in the task versus $Variables.
“Is the task taking data FROM the field group OR sending data TO the field group?”
FROM = $FieldGroup.FieldNames go in INPUT fields and $Variables go in OUTPUT fields.
TO = $FieldGroup.FieldNames go in OUTPUT fields and $Variables go in INPUT fields.
Field Groups = Multi-rows
I think of field groups as ‘related tables’ where there can be multiple rows of data associated with one ‘parent row’. But it’s really a bit more complicated than that in that you can have sub-groups. We don’t do anything complicated with them, so my knowledge is limited.
Also - field groups interact with certain task elements that allow for multi-row processing. A lot of tasks do this - so you have to read the task descriptions carefully. For example, processing each line of a PO or Sales Order - they have to have a datalink or integration-datalink to process each row, but you do that with one call to the Datalink-Field Group task and pass it the Field group. Then it processes each row in the Field Group independantly.
So as an example, the Action/Tasks would look something like this?
SQL table has these fields:
Order, Sequence, Detail1, Detail2, InvoiceNo
Detail3 is blank for now
Tasks:
Display Message prompts the user to Enter Order and Sequence in line items
Datalink Field Group:
Inputs: $Field.Order and $Field.Sequence
Outputs: $Field.Detail1, $Field.Detail2
Direction is ‘from’
The above works and I can see all 4 values in the line items. For now InvoiceNo is empty.
Now, I have another datalink that updates the InvoiceNo field in the SQL table based on one of the content fields. I want to then get that value to show up in the line items, but can’t get it to work without all the other line data disappearing.
Sorry, I just keep hitting one roadblock after another and have been at this for days.
Datalink Field Group: (uses the same field group as above)
Inputs: $Field.Order, $Field.Sequence
Outputs: $Field.InvoiceNo
I tried outputting all the fields, just that one field, from/to etc.
The error says the workflow can’t proceed because the field groups would be overwritten.
I think it might be the multi-row processing and it sort of happens automatically. (See below)