Capture the date of when a form was last submitted

[AdamH]

Hi All, 

I want to capture the date of when a form is submitted and save this to another SP field. 

I've seen some posts where you can use the default SP fields as to when a 'specific' form was submitted but my need is slightly different because I only want to capture when the LAST form was submitted. 

So my initial thought was to include something within the 'Save Button' [OnSelect] = SubmitForm(Form5_6); Back() 

But I also want to add a condition If(NewForm) Because I don't want the captured date to update if a form is opened and amended. 

The only other thing I need to consider, is that I am using one-to-many relationships, so the form itself is stored in another SP List whereas the date of the last form submitted that I want to capture, is saved in to the main SP list for the App. 

Main SP List: NC MST Tracker

SP List for forms: Apprentice Progress Reviews

Anyone able to assist? 

[rubin_boer]

hi @AdamH apology if i misunderstand this. 

I know you said you have looked at the defaults but it seem that is can provide your requirement. Latest date when submitted but not amended. Some clarity, how is this different from 'created' date?

If you want to see the date for the latest entry it will be (SP defaults):

The created where the form was in "New" mode

The 'created' where the 'modified' is the latest entry, where the form was in "Edit" mode

In both cases the date you will be returning is the one with the latest 'modified' date, which in essence will remain the 'created' date.

Hope it helps,

R

[Waegemma]

Hi AdamH,

Add the 'Id(ID)' field to your form, set it to invisible because you only need it to check if it's blank or not.

In the Onselect of the submit button, add If(IsBlank(DataCardValueX); insert your code here ) before SubmitForm.

DataCardValueX being the value of the 'Id(ID)'-card.

Hope I understood you question correctly 🙂

[AdamH]

Hi @rubin_boer 

I'm not sure that you misunderstood as such, maybe my explanation wasn't thorough enough - or you are right with you answer and I just don't see the answer 🙂  

Let me run an example of my application by you. 

Every month we complete a form on a staff member with their progress. I have a 'dashboard' with all the staff listed on it and want to display on that 'dashboard' the date of the last form completed. That will enable me to then go through the dashboard and highlight any staff that have not had a review (form submitted) this month. Next month, we start again and complete a new form and when that form is submitted, the date on the dashboard automatically updates with the date of the last form, then again I can see if someone has been missed and needs a review for (June). 

Hopefully that makes more sense? 

[rubin_boer]

hi @AdamH when you say dashboard, may i ask what do you use for the dash board? I get it now and you can get away with using a single sp list as you have now, im just about to take a trip but can share some ideas later.

[Waegemma]

Hi AdamH,

Seems like you could use a TemplateFill or another indication to show if a form was submitted this month. You could set that indication to change if the date of the last form submitted is later than the beginning of this month:

If(DateLastFormSubmitted> Date(Year(Today()), Month(Today(), 1), Black,White)

That way you don't have to manually highlight 🙂

Marc

[AdamH]

Hi @Waegemma 

I suppose that is a way of doing it - With my application though as we are a team and a large organisation, I wouldn't want to complete a progress review on the 31st of May and on the first of June a colleague of mine would notice that a review has not been completed in June, and book a progress review for the 1st of June. 

But I like the idea and I might be able to use that somewhere else in the application 

[AdamH]

Hi @rubin_boer 

When I say 'dashboard' what I mean is a gallery pulling in what data I want to display in one screen. Could be Name, Employee Number, Age etc. 

The idea is to have the last submitted form (DATE) shown which makes it easier looking through 40 / 50 records as to who has completed their monthly reviews. 

[Waegemma]

Hi AdamH,

Last attempt 🙂

Change the NC MST Tracker gallery Items to:

AddColumns(NC MST Tracker,"DateLastFormSubmitted", Last(SortByColumns(Filter("Apprentice Progress Reviews",ThisRecord.EmployeeID = EmployeeID), "Created",    Ascending)).Created)

This should get you the date of the last form created for each employee, in an extra column with the name "DateLastFormSubmitted".

ThisRecord.EmployeeID is the EmployeeID you get from NC MST Tracker,

the next EmployeeID in the formula is the one you use in Apprentice Progress Reviews.

Hope this helps,

Marc

Marc

[AdamH]

Hi @Waegemma 

Sorry for the delay in getting back to you on this, I had something urgent come in I needed to deal with.

I had some code in the Gallery [Items] already and I'm just struggling to get it to work together with your suggestion.

Any suggestions?

Filter('NC MST Tracker', Active.Value="Yes",Grade.Value = "A-EMT" Or Grade.Value = "A-ECSW"); AddColumns('NC MST Tracker',"DateLastFormSubmitted", Last(SortByColumns(Filter('Apprentice Progress Reviews',ThisRecord.RefID = RefID), "Created", Ascending)).Created)