cancel
Showing results for 
Search instead for 
Did you mean: 
hpkeong

Apps with Batch Saving & Single Record Updating [Part 1]

CRUD with single record addition, updating or deleting, has no doubt, the most common practice when dealing with database in most of the industries.

 

However, Batch saving of data has always been the most efficient way of data input especially in a group of identical source.

Before the introduction of ForAll function, I had posted in the forum using Timer.OnTimerEnd, Collection and Patch to save a batch of data thru filtering and patching Single Data (record) at a time until the end of the row of the collection. Though the process is a real pain, but it did help temporarily solving batch data saving.

 

Upon request from messages and someone in the forum, esp. @OlaH (from Sweden), and with hints from @mr-dang (from USA), I have developed a complete Batch Saving & Single Record updating Apps and, hopefully, this will further clarify how to use:

- Patch

- ForAll

- UpdateIf

- ClearCollect

- UpdateContext, etc.

  and share a complete workaround to serve as a simple, yet practical guide in developing related apps.

 

In Part 1, I will show how to start from scratch, a complete sample app UI design, based on the idea put forward by Ola H from Sweden (sharing of the overall concept is with the consent from him! Thanks), which was completed within one year, from 31/Dec/2016 to 1/12017 and fine tuned last week. (well, in fact, with the clear guidance here, you may only need 2 hours. This was what I had achieved from scratch)

 

1. DataSource in Excel

- I have created three Tables (in Excel, saved in Dropbox) (Note: I only use Excel in Cloud or CDS, but rest assure, it does not affect the application when used with Sharepoints or others)

- Bind all the tables to PowerApps

(a) TableTeacher

(b) TableStudentList

(c) TableAttendance

 

Both TableTeacher and TableStudentList will serve as BaseData, and this shall then be filtered and appended into Gallery1.items = TableAttendance to keep track, on daily basis, the attendance of students (in BATCH) & Update when attendance of particular student is changed.

 

Daily Attendance List trackerDaily Attendance List tracker           Used as Base DataUsed as Base DataUsed for Dropdown TeacherUsed for Dropdown Teacher

You may see, in the initial UI design, with:

1. OnScreen.Visible =

ClearCollect(StudentList, Filter(TableStudentList, Grade in FirstN(Distinct(TableStudentList, Grade),1))); UpdateContext({X:0})

- This serves to collect TableStudentList into a collection named = StudentList [ClearCollect(StudentList...]

- Filter with FirstN to show the data for first Grade, that is 6A, by default, in this example. [Grade in FirstN(Distinct(TableStudentList, Grade),1). You may opt-out this!

 

Add a Gallery1 with:

Gallery1.Items = StudentList

As I had explained in several post in the PowerApps forum, CollectionName is used as Items in Gallery, and is very useful and practical to:

- speed up data presentation

- use for multil-search / filter and many other purposed. 

 

2. Add a TextBox or Button for Date =

- Button1.Text (Today-Date) = Text(Today()+X, "[$-en-US]dd/mm/yyyy")

- Button1.OnSelect = UpdateContext({X:0})... this is update the date to "Today's date"

- ">": Arrow1.OnSelect = UpdateContext({X: X+1})...One day increment

- "<": Arrow2.OnSelect = UpdateContext({X: X-1})...One day decrement

 

- UpdateContext({X:0}) as shown in No. 1 at the end of ClearCollect(....): serves as a control key to Button1.Text where X will be used as increment or decrement to show day before or day after by clicking on "<" or ">".

 

 

Tips:

1. The reason of using Button is because Click or Touch on Button has a better response (I tried before and yes, Button is better).

2. Meantime, by touching on ">", "<", you will find the gallery will show the corresponding Students' attendance status almost in real time. This is crucial! (I will show a Video Presentation at the end of final Part).

 

3. Add two Dropdowns with =

- DropdownGrade.Items = Distinct(TableStudentList, Grade)

- DropdownTeacher.Items = Distinct(TableTeacher, Teacher)

 

Note: For students counter, this is just an added information. This will be explained later in other Part.

 

Page onVisiblePage onVisible   Galley1 showing Screen.OnVisible resultGalley1 showing Screen.OnVisible result

 

What is intended to be attained by the Apps here are:

1. When the Teacher selects / click on GRADE dropdown, the gallery will show the Student Name List based on the selected Grade,

2. By default, I have made the Toggle.Default = false and show them in RED Color

3. Reason of Absent is preset in Dropdown, as

-DropdownReason.Items = [" - ", "Sick", "Playtruant", "Others"]

4. What is distinctive here is, when none of the student from the selected Grade (and the selected date) are yet to be saved into Excel - TableAttendance, the SAVE Icon (A Check inside a Document symbol) will appear on Top-Right, which will be used for BATCH SAVING.

[Once data of the selected Grade & Date is available in Excel, this will disappear, to alert the Teacher]

 

In the NEXT Chapter, I will continue to show the formula / coding for:

- Student Name, Toggle and Dropdown inside Gallery1

- Batch Saving

 

Hope you enjoy my sharing and see you next week...

 

Comments

Many thanks for the tutorial I have a quick question though...

 

When I the following:

2. Add a TextBox or Button for Date =

- Button1.Text (Today-Date) = Text(Today()+X, "[$-en-US]dd/mm/yyyy")

 

I get an Information icon that states "The values being comapred have mismatched types (left is a number and right is Text)

So the date does not display in the button, could you please advise?

 

Many thanks

Hi

 

Thanks for reading my blog.

 

I try to screenshot and share with you. I do not any specific formatting to the Button.

- Button.Text = Text(Today()+X, "[$-en-US]dd/mm/yyyy")

(The Button is named Today-Date or any name you wish).

 

My actual sample appsMy actual sample apps

When or where do you see the error?

On the Button.Text, nothing shall appear.

The comparison only occur when you start changeing the date using <. >, and the TOGGLE will start to trigger and compare. I guess your Toggle might have problem.

If(LookUp(TableAttendance, Date = 'Today-Date'.Text && Grade = DropdownGrade.Selected.Result && StudentName = 'Student-Name'.Text).Attendance = "True", true, false)

 

Comparison is within Toggle, to the "Today-Date". Please take note. This is my trick of speeding up data processingComparison is within Toggle, to the "Today-Date". Please take note. This is my trick of speeding up data processing

 

Please double check and get back to me to clarify others.

 

NOTE:

I can send you the original Apps and Excel for you to learn and modify.

Just private message me your email will do.

 

TQ

 

Thank you very much for replying!

I had misunderstood that you NAMED the button Today-Date, and I was attempting to use that it in the forumula of the Text.

 

Sorry for the confusion and again, many thanks for posting this tutorial, it is almost exactly the type of app I am attemping to create for my school, but for use with detention attendance instead, so I am modifying things as I go to fit my needs.

 

Many Thanks

 

The section regarding the toggles does not appear in part 2 of your guide, I think you might have forgot to put it in?

 

Many thanks

Hi

 

Thanks for reporting, I will look at it then.

 

Best regards.

Hi hpkeong,

 

I have downloaded your app and use the data source by creating a new excel file, but seemed like unable to compile, do you mind to share your excel file with me. Thanks

 

Best Regards

Lennon

 

 

Anonymous

Hi,

 

Thank you for the sharing. Can I have the copy of the app and excel file for me to re-create the apps for learning purposes.

 

khairahnazurah.karim@pos.com.my

 

Thank you

Anonymous

Hi,

 

Thank you for the sharing. Can I have the copy of the app and excel file for me to re-create the apps for learning purposes?

 

Thank you