This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.
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?
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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?
I think this is from how you manually are editing the drop down data connections and attempting to filter.
SharePoint columns and values are the same as what you see in SP. It is the default view that IP consumes.
SharePoint columns and values are the same as what you see in SP. It is the default view that IP consumes.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
InfoPath
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Basic 264 lessons
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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().