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[OpLocationCode = @OpLocationCode]

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:

Instead the tracking number looks like this:

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Clay FoxDirector of Business IntegrationCommented:
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().
-PolakAuthor Commented:
Hi Clayfox, glad to see you are still on here. So I followed about 60% of what you said....
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)
-PolakAuthor Commented:
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[OpLocationCode = @OpLocationCode]
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Clay FoxDirector of Business IntegrationCommented:
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.
-PolakAuthor Commented:
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?
Clay FoxDirector of Business IntegrationCommented:
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.

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 trial
It'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

From novice to tech pro — start learning today.