Setting a Global Variable to the field value of a Collection

[adove]

I want to create a Global Variable (varMemoBody) in the OnStart portion of my app. 

1. How do I initialize that variable to be null from the start?

I currently have Set(varMemoBody, ''); but that throws an error. I suppose I could assign it a dummy text Set(varMemoBody, "sample"); but that seems messy.

On a subsequent screen I want to update/set that "varMemoBody" Global Variable to a specific field in a tabular collection of data. The collection, which is called "MemoCollection", is an imported dataset from a SharePoint list so it has multiple columns and rows.

2. How do I set or where do I set the new "varMemoBody" variable? There doesn't seem to be an OnStart on subsequent screens?

3. I want to assign a specific field value on my "MemoCollection" to the "varMemoBody". What is the syntax for my formula? I am interested in filtering on the "Title" column (in this example I want 'widgets') and then assign the corresponding 'Body' column value to the "varMemoBody" variable. My end goal is to use the 'Body' column value in my global variable.

Thank you,

ADove

[rsaikrishna · Accepted solution]

@adove 

For your 1st questions, you can set blank value to the variable as below:

Set(varMemoBody, Blank())

I could not understand the question 2 and 3. 

Can you write up the usecase of your scenario? 

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

[adove]

I have a collection named "MemoCollection" which is an import from a SharePoint list. This list has multiple rows and columns. How would I set a global variable "varMemoBody" to equal the value of a particular field in the "MemoCollection"?

Let's take the following example, assuming that the grid is my "MemoCollection" and the users along the first column are my titles. Based on the user's favorite foods, how would I assign "varMemoBody" to equal the value of "tea"? I am looking for Sally's favorite drink to be assigned the value of "varMemoBody".

	Fruit	Drink	Meal
John	Apple	Milk	Cereal
Sally	Orange	Tea	Sandwich
George	Grape	Water	Soup
Mary	Peach	Coffee	Burrito

[crowleysoftware]

I don't have a way to try this at the moment but see if it works for you. You lookup where "Title" is Sally. Then you return the First of the returned rows and pluck out the Drink field.

Set(varMemoBody, First(LookUp(MemoCollection, Title = 'Sally')).Drink)

[adove]

I like this reply, but it won't work in my case. While in my example the field values were single words, my real-world values are paragraphs of text so I cannot filter on that. Is there a way to get the same result by using the user's name (title column) and drink column?

[adove]

This is my attempt at grabbing the value from my collection, but it does not populate with anything:

Set(varMemoBody, LookUp(MemoCollection, Title="Sally", Drink));

[crowleysoftware]

There must be something else wrong. This works fine for me. I can assign varMemoBody to a label and see "Tea".

Collect(MemoCollection, 
{
    Title: "John"
    , Fruit: "Apple"
    , Drink: "Milk"
    , Meal: "Cereal"
},
{
    Title: "Sally"
    , Fruit: "Orange"
    , Drink: "Tea"
    , Meal: "Sandwich"
});

Set(varMemoBody, LookUp(MemoCollection, Title="Sally", Drink));

[adove]

@crowleysoftware 

There was something else wrong. My data was not loading in the OnStart so my collection was empty.

I can get the collection to load if I use a button to manually load the collection, but that is not what I want.

How do I get the collection to load and set the variable on the loading of a particular screen?

This is what I have in my OnStart:

Set(varMemoBody, Blank());
Collect(MemoCollection, 'Memo Text');
Set(varMemoBody, LookUp(MemoCollection, Title="Sally", Drink));

But it does not actually work. It is as if the code doesn't fire. Now mind you, this is in the OnStart. If I can get this to work, I'd like it to function on a subsequent screen and not at the App level. Any ideas?

Alex

[crowleysoftware]

The code I posted was tested in OnStart so I'm not sure whats going on. You could try to see if there were any errors with the operation with

Errors(MemoCollection)

[adove]

@rsaikrishna 

helped me with calling a collection and calling my SharePoint list directly in the code from a subsequent page.

On a subsequent screen, I needed to use the OnVisible property (similar to the OnStart from the App screen) to load my code.

My code to pull in data from the SharePoint list is as follows and set it to a global variable:

Set(varMemoBody, LookUp('mySPList', Title="Sally", Drink));