SharePoint Integration

Re: Filter field not returning expected value from SharePoint List

jenielle — Tue, 07 May 2019 15:14:05 GMT

Well, that may very well be possible....the business just told me this morning that they went into the SharePoint list and "updated" the APOLLO company entries. Your explanation makes perfect sense. Thank you for that. I will have to have a talk with this team about change control...this solution was scheduled to go LIVE yesterday and this change they made essentially was a show stopper. Thank you again so much for helping to repair this and get us going again. I am glad we can release this today! Couldn't have done this without you.

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Tue, 07 May 2019 15:06:11 GMT

So - is it possible that previously there weren't any GLs that belonged to both companies? Because here is what is happening - you have GL 123 and for CompanyA the description is "One Thing" and for CompanyB the description is "Another". When you pick GL 123, the formula that only filters on the GL is going to return the first description it finds that matches. Depending on the sort order of your list items, it will ALWAYS choose "One Thing", for example, and NEVER choose "Another". When we add the filter for Company, now we are telling InfoPath "Please go fetch me the data from the row where the GL is 123 and the Company is the company I selected" instead of "Get me the first row that has a GL of 123". Does that make sense?

Re: Filter field not returning expected value from SharePoint List

jenielle — Tue, 07 May 2019 14:56:23 GMT

Thanks again, Hilary! You know what is just so puzzling here is, this field worked properly once upon a time, filtering and displaying the proper values. I'm confused as to what happened and caused it to stop working. There are other fields in the form which are similarly configured and they work without the secondary condition. I'm telling you, this stuff really hurts my brain sometimes! LOL

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Tue, 07 May 2019 14:51:02 GMT

I knew you could get this. :)

Re: Filter field not returning expected value from SharePoint List

jenielle — Tue, 07 May 2019 03:44:28 GMT

Hilary - you are a SUPER HERO! I never would have figured this out. This filter stuff is still a little advanced for me. THANK you for helping me with this. I guess I didn't understand what you were saying at first. So I still messed up a couple times because I didn't know how to get into the area to add this second condition on the filter. But once I did - it worked! I'm telling you, I want to be just like you when I grow up - you're absolutely amazing! Thank you for being patient with me. I will be going through my form to apply these changes to the other fields. Enjoy the remainder of your week!

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Mon, 06 May 2019 22:56:45 GMT

My screenshot was from your own rule - I only added the company filter. Can you try the form I attached and see if the rule to set the description for the first row in your table of GLs works correctly when you select a GL that is the same between the two companies but has a different description?

Re: Filter field not returning expected value from SharePoint List

jenielle — Mon, 06 May 2019 22:45:36 GMT

Hi Hilary - Sorry, I had to step away to pick up the kids!

So GLs 1 - 12 are separate fields from InvGL, so the condition to display results if GL1=InvGL would never be possible.

Essentially, an invoice approver can allocate 100% of an invoice to a single GL and cost center (InvGL) OR they can split the amount of the invoice amongst up to 12 different cost centers (GL1-GL12).

The SharePoint list has the 3 columns. We want to first filter the InvGL choices available to the user based upon the company selected, so it either allows users to select from KFCU/KFIS GLs or APOLLO GLs. Then once the list is filtered, once they select the desired GL for that company we want the selected GL to automatically populate the description field with the corresponding description from the list.

From what I can see, the list has 28 GLs which are shared by both companies, 7 of which have unique descriptions to them for each company. So while in most cases the filter failing and displaying the description associated with the GL, including the 21 GLs wherein the GL number and description are the same for both companies, it IS an issue on the 7 GLs for each company wherein the descriptions are not the same.

So, if I can't add an additional filter on the SetGL rule, I wonder what it is going to take to get this working.

And I am MORE concerned that it once worked, and spontaneously stopped working.

The same type of logic/rule is applied to the InvCC and description fields and those are working...I'm going to SEE if I can somehow replicate the rules there...

Thank you SO much for trying to help me figure this out!

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Mon, 06 May 2019 21:14:04 GMT

Here's a copy of your form w/ this change made on the rule on GL1.

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Mon, 06 May 2019 21:13:30 GMT

Sorry, I must not be being clear. Do your dropdowns in the GL column display the correct options? Ohhhhhh, I see what you are saying. You know what? I think you just need a second filter on your SetGLDescription rules - you need to compare on title AND on company.

Re: Filter field not returning expected value from SharePoint List

jenielle — Mon, 06 May 2019 21:05:18 GMT

Hi Hilary - according to the hidden table you set in the form, it appears that when the company is selected, the corresponding invoice GLs is highlighting. which is why I'm thinking the invoice GLs actually is working. However, there are a couple GLs in particular wherein I know both companies share the same number, but different descriptions. When I play with those GLs, no matter which company I select, the APOLLO description is the only one that displays...so this is why I am now thinking maybe its the description field?

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Mon, 06 May 2019 20:59:34 GMT

Ok, maybe I'm confused too. So, your GL dropdown does populate correctly? It is just that the description is not getting set?

Re: Filter field not returning expected value from SharePoint List

jenielle — Mon, 06 May 2019 20:10:13 GMT

Hmmm...something is amiss...maybe with the description??? When I select the companies, the corresponding block of GLs highlight, which indicates the company piece is working. However when I select the GL, the description does not always align... hmmmm...

Both KFCU/KFIS and APOLLO have a GL "271001-0000"

For KFCU/KFIS, the description should be MARKETING, and for APOLLO, it should be "Referral Fee". Yet, no matter which company is selected, the Referral Fee is displayed in the description.

So maybe it has something to do with the fact that this particular GL number is used twice/not unique...but why would it not display the corresponding/correct description?

I have a rule on the InvGL field called "SetGLDescription" which sets the GL Description field and the Value field is a formula. Maybe the issue is with this field, rather than the invoice GL field?

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Mon, 06 May 2019 19:52:35 GMT

Here's another copy of your form with a test area added at the bottom - it has a repeating table bound to the Invoice GL data source. It has conditional formatting on the rows to highlight them if they match the selected Company in the form - save the file locally, right click and select Design. Preview the form, and try both the company radio buttons to verify the right rows highlight - this may help us figure out the issue.

Re: Filter field not returning expected value from SharePoint List

jenielle — Mon, 06 May 2019 19:43:29 GMT

Hi Hilary, Sorry for the miscommunication. Yes, the Company is a radio button, and the selections are all exactly the same, in upper case ("KFCU/KFIS" and "APOLLO"). These were all working just fine once upon a time, and I didn't make any changes to the configuration - it just suddenly stopped working and I am not sure why, and I haven't been able to restore the functionality.

Re: Filter field not returning expected value from SharePoint List

Hilary Stoupa — Mon, 06 May 2019 19:32:25 GMT

So - in your SharePoint List - what does the Company field look like? Is it exactly the same as the values for your radio buttons, including case? I see the value of the buttons is all upper case.... I had misunderstood and thought this was cascading dropdowns, but your Company field is a radio button. :)