cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nlsh
Frequent Visitor

How to make "with" command run everytime?

Hello, I have this section of code in the "On Visible" Section of a screen which mainly displays available dates for a workspace. The issue I am having is that the "with" command doesn't seem to run when I select this screen since the dates fail to update. Help would be appreciated. Code is below:

 

ClearCollect('Bay Day','Workshop Planner');
Refresh(Queue);
ClearCollect(EarliestAval,Queue);
With({_items: Sort(EarliestAval, 'Date Out')},
Set(Date1, LookUp(_items, Title="1").'Date Out');
Set(DateRB, LookUp(_items, Title="Reference Bay").'Date Out');
Set(DateSL, LookUp(_items, Title = "Service Lane").'Date Out');
Set(Date2, LookUp(_items, Title = "2").'Date Out');
Set(Date3, LookUp(_items, Title = "3").'Date Out');
Set(Date4, LookUp(_items, Title = "4").'Date Out');
Set(Date5, LookUp(_items, Title = "5").'Date Out');
Set(Date6, LookUp(_items, Title = "6").'Date Out');
Set(Date7, LookUp(_items, Title = "7").'Date Out');
Set(Date8, LookUp(_items, Title = "8").'Date Out');
Set(Date9, LookUp(_items, Title = "9").'Date Out');
Set(Date10, LookUp(_items, Title = "10").'Date Out');
Set(Date11, LookUp(_items, Title = "11").'Date Out');
Set(Date12, LookUp(_items, Title = "12").'Date Out');
Set(Date13, LookUp(_items, Title = "13").'Date Out')
);
With({_items: Sort(EarliestAval, 'Date In')},
Set(Date1_0, LookUp(_items, Title="1").'Date In');
Set(DateRB_0, LookUp(_items, Title="Reference Bay").'Date In');
Set(DateSL_0, LookUp(_items, Title = "Service Lane").'Date In');
Set(Date2_0, LookUp(_items, Title = "2").'Date In');
Set(Date3_0, LookUp(_items, Title = "3").'Date In');
Set(Date4_0, LookUp(_items, Title = "4").'Date In');
Set(Date5_0, LookUp(_items, Title = "5").'Date In');
Set(Date6_0, LookUp(_items, Title = "6").'Date In');
Set(Date7_0, LookUp(_items, Title = "7").'Date In');
Set(Date8_0, LookUp(_items, Title = "8").'Date In');
Set(Date9_0, LookUp(_items, Title = "9").'Date In');
Set(Date10_0, LookUp(_items, Title = "10").'Date In');
Set(Date11_0, LookUp(_items, Title = "11").'Date In');
Set(Date12_0, LookUp(_items, Title = "12").'Date In');
Set(Date13_0, LookUp(_items, Title = "13").'Date In')
);
If(Date1 = Blank(), Set(Date1,First(Filter('Bay Day', Title = "1").field_2).field_2));
If(Date2 = Blank(), Set(Date2,First(Filter('Bay Day', Title = "2").field_2).field_2));
If(Date3 = Blank(), Set(Date3,First(Filter('Bay Day', Title = "3").field_2).field_2));
If(Date4 = Blank(), Set(Date4,First(Filter('Bay Day', Title = "4").field_2).field_2));
If(Date5 = Blank(), Set(Date5,First(Filter('Bay Day', Title = "5").field_2).field_2));
If(Date6 = Blank(), Set(Date6,First(Filter('Bay Day', Title = "6").field_2).field_2));
If(Date7 = Blank(), Set(Date7,First(Filter('Bay Day', Title = "7").field_2).field_2));
If(Date8 = Blank(), Set(Date8,First(Filter('Bay Day', Title = "8").field_2).field_2));
If(Date9 = Blank(), Set(Date9,First(Filter('Bay Day', Title = "9").field_2).field_2));
If(Date10 = Blank(), Set(Date10,First(Filter('Bay Day', Title = "10").field_2).field_2));
If(Date11 = Blank(), Set(Date11,First(Filter('Bay Day', Title = "11").field_2).field_2));
If(Date12 = Blank(), Set(Date12,First(Filter('Bay Day', Title = "12").field_2).field_2));
If(Date13 = Blank(), Set(Date13,First(Filter('Bay Day', Title = "13").field_2).field_2));
If(DateRB = Blank(), Set(DateRB,First(Filter('Bay Day', Title = "Reference Bay").field_2).field_2));
If(DateSL = Blank(), Set(DateSL,First(Filter('Bay Day', Title = "Service Lane").field_2).field_2));

6 REPLIES 6
RandyHayes
Super User
Super User

@Nlsh 

Let's start with changing your formula to the following:

ClearCollect('Bay Day','Workshop Planner');
Refresh(Queue);
ClearCollect(EarliestAval,Queue);

With({_items: Sort(EarliestAval, 'Date Out')},
    Set(Date1, Coalesce(LookUp(_items, Title="1").'Date Out', LookUp('Bay Day', Title="1", field_2)) );
    Set(DateRB, Coalesce(LookUp(_items, Title="Reference Bay").'Date Out', LookUp('Bay Day', Title="Reference Bay", field_2)) );
    Set(DateSL, Coalesce(LookUp(_items, Title = "Service Lane").'Date Out', LookUp('Bay Day', Title="Service Lane", field_2)) );
    Set(Date2, Coalesce(LookUp(_items, Title = "2").'Date Out', LookUp('Bay Day', Title="2", field_2)) );
    Set(Date3, Coalesce(LookUp(_items, Title = "3").'Date Out', LookUp('Bay Day', Title="3", field_2)) );
    Set(Date4, Coalesce(LookUp(_items, Title = "4").'Date Out', LookUp('Bay Day', Title="4", field_2)) );
    Set(Date5, Coalesce(LookUp(_items, Title = "5").'Date Out', LookUp('Bay Day', Title="5", field_2)) );
    Set(Date6, Coalesce(LookUp(_items, Title = "6").'Date Out', LookUp('Bay Day', Title="6", field_2)) );
    Set(Date7, Coalesce(LookUp(_items, Title = "7").'Date Out', LookUp('Bay Day', Title="7", field_2)) );
    Set(Date8, Coalesce(LookUp(_items, Title = "8").'Date Out', LookUp('Bay Day', Title="8", field_2)) );
    Set(Date9, Coalesce(LookUp(_items, Title = "9").'Date Out', LookUp('Bay Day', Title="9", field_2)) );
    Set(Date10, Coalesce(LookUp(_items, Title = "10").'Date Out', LookUp('Bay Day', Title="10", field_2)) );
    Set(Date11, Coalesce(LookUp(_items, Title = "11").'Date Out', LookUp('Bay Day', Title="11", field_2)) );
    Set(Date12, Coalesce(LookUp(_items, Title = "12").'Date Out', LookUp('Bay Day', Title="12", field_2)) );
    Set(Date13, Coalesce(LookUp(_items, Title = "13").'Date Out', LookUp('Bay Day', Title="13", field_2)) );
);

With({_items: Sort(EarliestAval, 'Date In')},
    Set(Date1_0, LookUp(_items, Title="1").'Date In');
    Set(DateRB_0, LookUp(_items, Title="Reference Bay").'Date In');
    Set(DateSL_0, LookUp(_items, Title = "Service Lane").'Date In');
    Set(Date2_0, LookUp(_items, Title = "2").'Date In');
    Set(Date3_0, LookUp(_items, Title = "3").'Date In');
    Set(Date4_0, LookUp(_items, Title = "4").'Date In');
    Set(Date5_0, LookUp(_items, Title = "5").'Date In');
    Set(Date6_0, LookUp(_items, Title = "6").'Date In');
    Set(Date7_0, LookUp(_items, Title = "7").'Date In');
    Set(Date8_0, LookUp(_items, Title = "8").'Date In');
    Set(Date9_0, LookUp(_items, Title = "9").'Date In');
    Set(Date10_0, LookUp(_items, Title = "10").'Date In');
    Set(Date11_0, LookUp(_items, Title = "11").'Date In');
    Set(Date12_0, LookUp(_items, Title = "12").'Date In');
    Set(Date13_0, LookUp(_items, Title = "13").'Date In')
);

 

If that does not get you what you need, then please site an example of one of the variables that is not set and what you have in your datasource that should correspond to it.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Nlsh
Frequent Visitor

Currently Date1 is set to 3/11/2022, however in the updated list, the value is now 3/22/2022. Hopefully this is what you are asking for

Nlsh
Frequent Visitor

Currently Date1 is set to 3/11/2022, however in the updated list, the value is now 3/22/2022. Hopefully this is what you are asking for

RandyHayes
Super User
Super User

@Nlsh 

What do you mean by "the updated list"?  We are working with the values of the variables - correct?  That was what you originally posted in your question - or are you stating some other problem?

Do the variables get the values you expect?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Nlsh
Frequent Visitor

So I have this flow which automatically replaces certain value in a list value based on the date. So if the "Date Out" column in SharePoint List Item "1" is 3/22/2022, the automated flow replaces all relevant column values with a list item in another SharePoint List which has a "Date In" value that is the same day as the "Date Out" column value that occupied that list item originally. However the Date1 and Date1_0 Values (for example), don't automatically update when I select the diagram which shows the dates available on the screen. Currently Date1 and Date1_0 are displaying date values from the SharePoint list item that previously occupied that spot. Hopefully that helps.

RandyHayes
Super User
Super User

@Nlsh 

Ah...so your original post heading makes a little more sense now - I think!

You mentioned you wanted this formula to run every time, but that it doesn't seem to.

If I am thinking what I am hearing now is correct, you are stating that the variables are not getting updated when you perform your flow action.

If that is all the case, then yes, this is true, variables will not update until they are updated in a formula.  Since you have this in the OnVisible, it will only occur when the screen becomes visible.

 

Let me know if I am tracking what the issues is that you are having.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,783)