Saving a timesheet

[Takami]

Dear Community, 

Thanks in advance for your help and sorry if my question sounds stupid.

I'm designing a simple time tracking app for my users.

My sharepoint data structure is very simple: 

User

Project

Date

nb of hours spent

I have built the following interface based on tutorials I found on the web.

Would you have any hints on how to store the data created with this Gallery ?

Tanks in advance

[StalinPonnusamy]

Hi @Takami

• Need to loop through the Gallery
• And use Patch

Sample (Create or Update)

ForAll(Gallery3.AllItems,
	Patch(
    	'Issue tracker',
    	If(
        	EditForm1.Mode = FormMode.New,
        	Defaults('Issue tracker'),
        	LookUp('Issue tracker',ID = SelectedId)
    	),
    	{
        	Title: textinput3.Text,
        	'Issue description': DataCardValue10.Text
    	}
	)
)

[RandyHayes · Accepted solution]

@Takami 

You actually have two conditions to consider - 1, creating these records and 2, editing records.

Your initial post seems more concerned over creating the records, so I will focus on that.

It would be good to know the Items property of your gal_Calendar_Days control as well as the name of the TextInput control in the gallery.

But, assuming that the gallery has an Items property that produces actual dates for each record, then your formula would be similar to this:

Collect(yourDataSource,
    ForAll(
        Filter(yourGallery.AllItems, !IsBlank(yourTextInput.Text)),
        {
           User: yourUserValue,
           Project: yourProjectValue,
           Date: Value, // see note below
           'nb of hours spent':  Value(yourTextInput.Text)
        }
    )
)

This will create a record for each cell in your calendar that has a value by creating a table with the ForAll function (which creates tables and is not a For/Loop function) and sends that to the datasource with a Collect function.

As for the Date column...this will completely depend on the Items property of your Gallery.  In the above, I assumed there was a Value column in your items that represents the date.

So, a lot of assumptions here because more detail is needed about your scenario, but this is the basic process.

I hope this is helpful for you.

[Takami]

Thanks both for your help, I nearly got to the end of it. Here is my submit button:

Collect('TimeTracking (Complex)';
    ForAll(
        Filter(gal_Calendar_Days.AllItems; !IsBlank(TimeBox.Text));
        {
           Utilisateur: User();  // this doesn seems to work (my SP list is expeting a user record)
           Projet: //not able to retrieve my dropbox selection
           Date: Value;
           Heure:  Value(TimeBox.Text)
        }
    )
)

The only issue I get now is to retrieve:

• The current user name: I thought user() function would do the job (it is not inserted in my Sharepoint list as a record)
• The selected project i'd like to insert in every generated record. It is a dropdown list that is out of the gallery which gets it's own data with the following formula:

Filter(Choices('TimeTracking (Complex)'.Projet); !IsBlank(Value))

I was not able to make a lookout function for my need.

Many thanks

[StalinPonnusamy]

Hi @Takami 

Updated Project and User

Collect('TimeTracking (Complex)';
    ForAll(
        Filter(gal_Calendar_Days.AllItems; !IsBlank(TimeBox.Text));
        {
           Utilisateur: {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Claims: "i:0#.f|membership|" & User().Email,
            Department: "",
            DisplayName: User().FullName,
            Email: User().Email,
            JobTitle: "",
            Picture: ""
        };
           Projet: {Value: ProjectDropdown.Selected.Value};
           Date: Value;
           Heure:  Value(TimeBox.Text)
        }
    )
)

[RandyHayes · Accepted solution]

@Takami 

No problem, you just need to account for the two columns.  Assuming the Utilisateur column is a person column in your list, then the formula would be this:

With({_user: 
    With(User();
        {Claims:"i:0#.f|membership|" & Lower(Email); 
         Department:""; 
         DisplayName: FullName; 
         Email: Email; 
         JobTitle: ""; 
         Picture: ""
         }
    )
  };
    Collect('TimeTracking (Complex)';
        ForAll(
            Filter(gal_Calendar_Days.AllItems; !IsBlank(TimeBox.Text));
            {
               Utilisateur: _user;
               Projet: {Value: yourProjectDropdown.Selected.Value;
               Date: Value;
               Heure: Value(TimeBox.Text)
            }
        )
    )
)

The User() function is expensive for performance and should only be called once - certainly not on every iteration of the ForAll function.  In the above the SharePoint user record is created outside of the ForAll with a With variable and then used within.

Getting to the value in your project dropdown is not a problem as long as you reference the name of the dropdown in the above formula.

Give that a try and you should be good to go.

[StalinPonnusamy]

Good point @RandyHayes 

My bad. Not much attention to forall loop. Yes for performance, Getting user information should be outside of for loop since we need to get it once.

[RandyHayes]

@StalinPonnusamy 

Yes, a lot of times people are already grabbing a snapshot of that in a variable in the OnStart, but on the forum, I can never assume...so, the With function to the rescue all the time.  But yes, it is expensive so only hit it once.

In fact, as a general practice (and tip for others) when working with any app that utilizes SharePoint and user columns is that, in the OnStart I have the following:

Set(glbAppUser,
    With({_user: User()},
        {User: _user,
         SPUser:
            {Claims:"i:0#.f|membership|" & Lower(Email), 
             Department:"", 
             DisplayName: FullName, 
             Email: Email, 
             JobTitle: "", 
             Picture: ""
            }
         }
    )
)

This way, any place in the app that I need current user info, this for example - glbAppUser.User.FullName

And when needing the SharePoint User record, it is this - glbAppUser.SPUser

By the way, the @odata.type column is not needed any longer.  That requirement was removed over a year ago.  Which is great because I could never, for the life of me, remember it!

[StalinPonnusamy]

Thanks @RandyHayes . I agree app onstart is a good place to have this.

[Takami]

Tanks all for your precious help, you are awesome. That worked perfectly.