Using Rules to Concat a Form Tracking Number
Hi Experts,
I'm attempting to use rules and filters to set the value of @TrackingNumber to create a unique value for my filename when submitting to SharePoint.
FYSA: I have a SharePoint List with OpLocationCode, OpRegion, OpBranch, OpUnit, and OpRegionCode all in a single SP List.
When a user makes a selection on the @OpLocationCode control all subsequent fields populate using rules and filters. For Example, when a change is made to @OpLocationCode the following rule/filter runs on @OpRegionCode @OpRegionCode[OpLocationCo
I have 4 of these rules/filters set up to cascade the correct values automatically into the controls that are dependent on @OpLocationCode.
At the end of these 4 rules/filters I have a 5th rule that attempts to Set the value of @TrackingNumber to:
concat(substring(now(), 1, 5), OpRegionCode, "-", OpLocationCode, "-", substring(now(), 6, 2), substring(now(), 9, 2), substring(now(), 15, 2), substring(now(), 18, 2))
The end result should make the tracking number look something like this:
2015-SER-HMU-02252956
Instead the tracking number looks like this:
2015--HMU-02252956
Basically, the "SER" which is @OpRegionCode is missing. Now I believe that is because the value for @OpRegionCode is blank at the time the 5th rule runs to set the tracking number. (I've tested by setting a condition that says run if @OpRegionCode is not blank and the rule does not run)
I realize that I could just have the tracking number set on form submit; however, I'd really just like to have it populate a tracking number when a user is done selecting an @OpLocationCode. Can I accomplish this?
I'm attempting to use rules and filters to set the value of @TrackingNumber to create a unique value for my filename when submitting to SharePoint.
FYSA: I have a SharePoint List with OpLocationCode, OpRegion, OpBranch, OpUnit, and OpRegionCode all in a single SP List.
When a user makes a selection on the @OpLocationCode control all subsequent fields populate using rules and filters. For Example, when a change is made to @OpLocationCode the following rule/filter runs on @OpRegionCode @OpRegionCode[OpLocationCo
I have 4 of these rules/filters set up to cascade the correct values automatically into the controls that are dependent on @OpLocationCode.
At the end of these 4 rules/filters I have a 5th rule that attempts to Set the value of @TrackingNumber to:
concat(substring(now(), 1, 5), OpRegionCode, "-", OpLocationCode, "-", substring(now(), 6, 2), substring(now(), 9, 2), substring(now(), 15, 2), substring(now(), 18, 2))
The end result should make the tracking number look something like this:
2015-SER-HMU-02252956
Instead the tracking number looks like this:
2015--HMU-02252956
Basically, the "SER" which is @OpRegionCode is missing. Now I believe that is because the value for @OpRegionCode is blank at the time the 5th rule runs to set the tracking number. (I've tested by setting a condition that says run if @OpRegionCode is not blank and the rule does not run)
I realize that I could just have the tracking number set on form submit; however, I'd really just like to have it populate a tracking number when a user is done selecting an @OpLocationCode. Can I accomplish this?
I think you know the issue, your value for OpRegionCode is blank when you are running this concatenation. Be careful that you do not have two very similar field names. @OpRegionCode is probably the data source value, and the value of the selection in your form may be OpRegionCode. You would want to concatenate the selection that was made and you should be able to see on your form.
I typically have a value for form name in the form's schema, just to make things easier and more flexible. This also helps a lot when editing forms since you can only set the value when it is blank (new) and retain the ID and filename when it is being updated.
I would set this as early as possible in the form's life.
Sounds like the users are picking from cascading dropdown fields to "file" the form, once those are selected and hopefully locked in, you should be able to generate the ID from the visible values and the now().
I typically have a value for form name in the form's schema, just to make things easier and more flexible. This also helps a lot when editing forms since you can only set the value when it is blank (new) and retain the ID and filename when it is being updated.
I would set this as early as possible in the form's life.
Sounds like the users are picking from cascading dropdown fields to "file" the form, once those are selected and hopefully locked in, you should be able to generate the ID from the visible values and the now().
Hi Clayfox, glad to see you are still on here. So I followed about 60% of what you said....
The only field that Users modify is @OpLocationCode (btw, yes, I agree its odd that users identify more with the code then the 4 dependent controls). There is a 1-for-1 match for the remaining 4 controls that are dependent on it, therefore the only thing setting the values for those 4 fields are the rule-filters I described above.
Where I'm lost is that I believe that for whatever reason before filtering the 4 dependent controls the rule sets their values to blank and THEN returns the correct filtered values.
Order of operations:
1. User selects @OpLocationCode
2. Rule/Filter Populates @OpRegion, @OpBranch, @OpUnit, and @OpRegionCode first as Blank
3. SetTrackingNumber concat rule runs creating the form number with a blank @OpRegionCode
4. Rule/Filter Populates the controls from #2 with their correct filtered values
5. SetTrackingNumber concat rule does NOT run again.
(PS I have tried to include all of the dependent controls in the concat formula, they are all blank as well when SetTrackingNumber runs)
I typically have a value for form name in the form's schema, just to make things easier and more flexible. This also helps a lot when editing forms since you can only set the value when it is blank (new) and retain the ID and filename when it is being updated.Can you do a "for example" here to help me understand the way you do it? Ala I don't just want to use "now()" to set the filename as there is a requirement for taxonomy when forms are just in a directory view (not a SharePoint view) AND I want the form tracking # to be visible before submission.
The only field that Users modify is @OpLocationCode (btw, yes, I agree its odd that users identify more with the code then the 4 dependent controls). There is a 1-for-1 match for the remaining 4 controls that are dependent on it, therefore the only thing setting the values for those 4 fields are the rule-filters I described above.
Where I'm lost is that I believe that for whatever reason before filtering the 4 dependent controls the rule sets their values to blank and THEN returns the correct filtered values.
Order of operations:
1. User selects @OpLocationCode
2. Rule/Filter Populates @OpRegion, @OpBranch, @OpUnit, and @OpRegionCode first as Blank
3. SetTrackingNumber concat rule runs creating the form number with a blank @OpRegionCode
4. Rule/Filter Populates the controls from #2 with their correct filtered values
5. SetTrackingNumber concat rule does NOT run again.
(PS I have tried to include all of the dependent controls in the concat formula, they are all blank as well when SetTrackingNumber runs)
So I just discovered more about my problem.... Disregard #4 and #5 above....
When the rule/filter runs to cascade @OpLocationCode to its 4 dependent fields it appears the correct value is in the fields. However, upon submitting this form for the first time to SharePoint... it appear that the filtered values in those 4 fields aren't values at all. It appears they are blank....
Why would this be? Again the filter is:
@OpRegionCode[OpLocationCo
When the rule/filter runs to cascade @OpLocationCode to its 4 dependent fields it appears the correct value is in the fields. However, upon submitting this form for the first time to SharePoint... it appear that the filtered values in those 4 fields aren't values at all. It appears they are blank....
Why would this be? Again the filter is:
@OpRegionCode[OpLocationCo
I would test this heavily, have the field result visible all the time, also promote all the component fields.
I would guess it is a timing issue, or the setup of the data connection.
I would guess it is a timing issue, or the setup of the data connection.
Whelp turns out it was the way I had my rules / filters set up. Oddly, in the now working version the data connected fields from the sharepoint list are appearing very peculiar in the rules/filter... for example:
b36n[OpLocationCode = Title]
"b36n" and "Title" I'm guessing being the names SharePoint assigns newly created columns... any issue with this?
b36n[OpLocationCode = Title]
"b36n" and "Title" I'm guessing being the names SharePoint assigns newly created columns... any issue with this?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial