31 AppSheet Expression Yang Sering Di Gunakan

Setelah sebelumnya kita latihan Appsheet dengan pengetahuan data, lalu di lanjutkan dengan pengetahuan mengenai Appsheet Editor dan 11 terminologi penting, maka disini diberikan penjelasan dan contoh dari expression yang biasanya akan di temui di aplikasi yang kita buat.

UNIQUEID()

Expression UNIQUEID() akan memberikan 8 digit kombinasi angka dan huruf (dalam text type) yang dijadikan sebagai identifier atau key dari setiap baris dalam suatu kelompok data (tabel). Secara statistic, 8 kombinasi tersebut memiliki kemungkinan duplikat yang sangat kecil.

UNIQUEID("UUID")

Penggunaan "UUID" (Universally Unique Identifier) dalam expression UNIQUEID() akan memberikan kelompok kombinasi dari 4 UNIQUEID() yang sesuai dengan penggunaan yang lebih memberikan kemungkinan duplikat yang hampir mendekati 0. UUID ini juga di panggil dengan GUID (Globally Unique Identifier) yang digunakan di banyak software lain, seperti dalam 3D design (Smartplant ataupun PDMS).

Expression UNIQUEID() akan secara otomatis di berikan oleh Appsheet sebagai bantuan apabila secara design, nama column mengindikasikan hal tersebut. Seperti penggunaan nama column MuridID atau ReportID akan secara otomatis di berikan UNIQUEID() di initial value.

Biasanya, diawal penggunaan Appsheet akan sedikit bingung dengan UNIQUEID() dan label, tapi seiring berjalan waktu, maka kita akan terbiasa dengan itu.

Penggunaan KEY atau identifier dari setiap baris data bisa saja menggunakan sesuatu yang lain, tergantung dengan tabel yang kita buat. Misalnya tabel user, maka bisa kita gunakan email sebagai KEY. Kadang, kita menginginkan suatu unique identifier yang berbeda, contohnya adalah nomor surat ataupun nomor PO, maka kita harus memperhatikan beberapa kondisi yang memungkinkan pengguna dari aplikasi dapat memasukkan data pada saat yang bersamaan dan menghasilkan text yang sama. Beberapa kombinasi itu seperti misalnya:

CONCATENATE( RIGHT(("000000" & ([_RowNumber] - 1)), 7), "-", UNIQUEID() )

(MID(“abcdefghijklmnopqrstuvwxyz”, RANDBETWEEN(1, 26), 1) & UNIQUEID())

CONCATENATE(UNIQUEID(),"-",UNIQUEID(),"-",UNIQUEID(),"-",UNIQUEID(),"-",UNIQUEID())

CONCATENATE(TODAY(), " ", UNIQUEID())

atau seperti ini:

Padasaat sudah lebih advance, kita bisa menggunakan system numbering seperti system booking ID yang akan mengontrol ID yang akan diberikan untuk digunakan. Ini seperti request document number untuk engineering document, atau request PR number untuk penggunaan di SAP, ataupun PO seperti contoh diatas lainnya. Dan bisa di kombinasikan dengan submission + Approval system agar memastikan bahwa text yang digunakan sebagai Key adalah unique - tidak ada lagi yang sama. Bayangkan apabila kita memiliki PO number yang sama untuk vendor yang berbeda, misalnya. Maka akan sulit untuk dapat menelusuri data terkait dengan PO number tersebut, seperti misalnya data invoice, data pengiriman barang, data pembayaran, dan lain sebagainya yang seharusnya dapat di hubungkan dengan PO number yang merupakan KEY.

Terkait dengan Key, Silahkan juga di lihat kembali Latihan Appsheet sebelumnya.

‍USEREMAIL()

USEREMAIL() akan memberikan alamat email dari user yang melakukan login (untuk aplikasi dengan kebutuhan login). Hart Exinity membangun corporate sistem, sehingga hampir seluruhnya membutuhkan login untuk aplikasi yang di develop dan menjadikan USEREMAIL() sebagai expression yang selalu di temui di setiap aplikasi yang di buat. Expression yang menggunakan USEREMAIL() bisa digunakan di mana saja, seperti penggunaan di contoh berikut:

1. Initial value: Untuk mengambil email address dari user dan input di database tabel.
2. Editable-If: Hanya dapat di edit oleh user yang melakukan entry data ataupun orang lain yang di tentukan
3. Show-If: Menunjukkan suatu column apabila user memiliki alamat email yang kita tentukan atau memiliki role yang di set.
4. App Formula: menghitung jumlah jam kerja dari user dan membandingkan dengan user lain, track siapa yang melakukan edit terhadap row.
5. Security filter: agar data yang di download HANYA data milik user itu saja, bukan user lain.
6. Slice: menampilkan data sesuai dengan user masing-masing, seperti outstanding action, open action item, dan lain-lain.
7. Key: sebagai key untuk user tabel atapun customer tabel.
8. Action valid-if: untuk show-hide action berdasarkan email user
9. Virtual column: sebagai filtering data, pengelompokan data berdasarkan email pengguna.

Berikut adalah beberapa contoh penggunaan USEREMAIL() dalam expression:

COUNT(

SELECT(

History[Rencana Hari ini],

[UserEmail] = [_THISROW].[UserEmail], TRUE)

)

// Untuk menghitung jumlah rencana hari ini di tabel history sesuai dengan USEREMAIL

IN(USEREMAIL(), TableName[Emails])

// Akan menghasilkan TRUE atau FALSE, dimana menjadi TRUE kalau USEREMAIL() ada di dalam column Emails di tabel yang di inginkan.

IN(USEREMAIL(), SELECT(Users[Email], ("Admin" = [Role])))

//Bisa digunakan sebagai slice untuk menunjukkan row dimana USEREMAIL() merupakan Admin di column Role.

FILTER(

 "Issues",

 AND(

   ([Status] = "Open"),

   ([Assigned to] = USEREMAIL())

 )

)

//Untuk melakukan filter dan menunjukkan issue tabel yang masih open dan di assign kepada saya.

OR(

 AND((TIMENOW() >= "9:00 AM"), (TIMENOW() < "5:00 PM")),

 IN(WEEKDAY(TODAY()), {"Saturday", "Sunday"}),

 IN(

   USEREMAIL(),

   SELECT(Users[Email], [Special Access?], TRUE)

 )

)

//Untuk memberikan access kepada user di jam dan hari tertentu.

TOTALHOURS(

 SUM(

   SELECT(

     Timesheets[Hours],

     AND(

       ([Employee] = USEREMAIL()),

       ([Date Worked] > (TODAY() - WEEKDAY(TODAY()))),

       ([Date Worked] <= (TODAY() - WEEKDAY(TODAY())) + 7)

     )

   )

 )

)

//Untuk menjumlahkan total jam kerja di minggu ini.

LOOKUP(

 USEREMAIL(),

 "Employee",

 "email",

 "manager name"

)

//memberikan nama manager untuk user yang sedang login.

Penggunaan USEREMAIL() bisa untuk column dengan type email ataupun text.

TODAY()

Expression TODAY() memberikan tanggal hari ini sesuai dengan device yang di gunakan oleh user. Sering digunakan sebagai intial value untuk column dengan tipe DATE untuk memudahkan user melakukan input data. Kadang kita menggunakan TODAY() untuk melakukan slice terhadap tabel agar bisa melakukan filter sehingga kita akan batasi view sesuai dengan hari ini saja, ataupun kita melakukan perhitungan dengan menggunakan virtual column dengan expression TODAY() sebagai salah satu faktornya. Beberapa contoh penerapan expression TODAY() antara lain:

(TODAY() - 1)

//Akan memberikan tanggal kemarin.

(TODAY() + 1)

//Memberikan tanggal esok hari.

(TODAY() + 7)

//Memberikan tanggal minggu depan.

(TODAY() - WEEKDAY(TODAY()))

//Memberikan tanggal di hari Sabtu minggu lalu.

(TODAY() - WEEKDAY(TODAY()) + 4)

//Memberikan tanggal hari Rabu di minggu ini.

HOUR(TODAY() - [TargetDate]) / 24

// Memberikan jumlah hari antara hari ini dengan target date.

(TODAY() = DATE([Data]))

//Memberikan seluruh data dimana tanggal di column data sama dengan tanggal hari ini.

SWITCH(WEEKDAY(EOMONTH(TODAY(),-1)+1),

1,IFS(DAY(EOMONTH(TODAY(),0))>28,DAY(EOMONTH(TODAY(),0))-29),

5,IFS(DAY(EOMONTH(TODAY(),0))<31,DAY(EOMONTH(TODAY(),0))-28),

6,IFS(DAY(EOMONTH(TODAY(),0))>28,1),

7,IFS(DAY(EOMONTH(TODAY(),0))>30,1), DAY(EOMONTH(TODAY(),0))-28

)

//Memberikan jumlah hari kerja dalam satu bulan kecuali hari sabtu dan minggu.

[Date Completed]>=TODAY()-2

//memberikan tanggal penyelesaian yang lebih besar dari 2 hari dari hari ini.

Untuk semua expression disini, kita juga bisa dengan mudah mencarinya di https://www.appsheet.com/Support.

Contoh berikut adalah apabila kita mencari kata TODAY, maka akan diberikan artikel dan juga contoh aplikasi yang terkait dengan expression TODAY itu. Contoh aplikasi bisa kita copy dan di pelajari untuk di terapkan fungsinya kedalam aplikasi yang kita buat.

Penerapan TODAY() akan banyak di temui untuk beragam aplikasi yang dibuat dengan appsheet. Bisa juga kita batasi data dengan menggunakan security filter dimana dibatasi data yang terdownload ke user device hanyalah data yang hari ini saja untuk mempercepat proses sync, mempercepat aplikasi dalam mengolah data dan memberikan fokus kepada apa yang harus dilakukan dalam waktu dekat saja.

NOW()

Expression NOW() akan memberikan tanggal dan waktu (jam, menit, detik) saat ini (sekarang) untuk DATETIME column yang sesuai dengan device yang di gunakan oleh pengguna aplikasi. Kadang kita menginginkan waktu yang tepat selain dari pada tanggal, maka kita bisa memilih DATETIME column dan gunakan NOW() sebagai intial value timestamp nya. Tergantung dimana kita akan gunakan expression NOW() tersebut, tapi beberapa contoh adalah sebagai berikut:

AND(MONTH([DATE])=MONTH(NOW()), YEAR([DATE])=YEAR(NOW()))

//Menunjukkan rows bulan ini.

EOWEEK(NOW())

//Sama seperti menanyakan hari sabtu minggu ini tanggal berapa?

DATE(NOW())

//Memberikan tanggal hari ini, seperti TODAY()

DATETIME(NOW())

//Tanggal dan jam sekarang

NOW() juga bisa di gunakan sebagai trigger untuk suatu action atau bot beroperasi. Atau di gunakan sebagai app formula untuk selalu memberikan kondisi terakhir kapan row tersebut di update.

‍

TIMENOW()

TIMENOW() sebenarnya sama seperti NOW(), tapi memberikan nilai jam sesuai dengan device yang di gunakan oleh user. Apabila kita hanya ingin mengetahui jam pasti kapan clock-in atau out misalnya, maka kita bisa gunakan TIMENOW() ini.

TIMENOW() + 1

// menambah 1 jam dari sekarang

TIMENOW() + "003:03:00"

// tambah 3 jam 3 menit dari sekarang

TIMENOW() - "003:03:00"

// 3 jam 3 menit yang lalu

(TIMENOW() - 1)

//satu jam yang lalu

(TIMENOW() + 1)

//1 jam lagi

(TIMENOW() + 24)

//besok di jam yang sama.

Agar di perhatikan penggunaan TIMENOW() untuk permanent column ataupun virtual column.

‍

UTCNOW()

Apabila kita mulai share aplikasi ke rekan kerja atau orang lain, akan mulai terlihat kebutuhan adanya suatu pengukuran waktu yang absolute yang tidak tergantung kepada device user. Kadang ada user yang jam di device nya tidak cocok karena di setting manual. UTCNOW() bisa digunakan untuk mengatasi hal seperti ini. Untuk menambahkan sesuai dengan waktu di Jakarta, misalnya, maka kita bisa lakukan +7 untuk menambahkan 7 jam dari waktu UTCNOW() tersebut, akan tetapi, aplikasi harus online karena UTCNOW() memberikan nilai waktu UTC di server appsheet berdasarkan waktu local user.

Silahkan di coba dan di test mana yang lebih cocok untuk kita gunakan di aplikasi kita.

(NOW() - UTCNOW())

//Akan memberikan durasi beda waktu antara user timezone dengan UTC.

[TIMESTAMP] + (TIME(RIGHT("0"&((FLOOR(TOTALMINUTES([TIMESTAMP] - UTCNOW()) + USERTZOFFSET())/60)*-1),2)&":00:00") - "00:00:00")

‍

ISBLANK()

ISBLANK() merupakan expression yang akan memberikan confirmasi apakah column/table/rows/list memiliki value. Tidak seperti beberapa expression lainnya, ISBLANK() tidak tergantung kepada type column.

Kami sering gunakan ISBLANK() untuk hampir semua logika aplikasi (Show_IF, Editable_IF, Required_IF ataupun lainnya).

Dengan menggunakan ISBLANK() kita bisa melakukan pengecekan apakah hasil dari expression akan memberikan hasil yang kita inginkan, ataupun kita bisa gunakan ISBLANK() untuk memastikan suatu column yang bukan key tidak memiliki duplicate value:

ISBLANK( FILTER( “NamaTabel”, AND( ISNOTBLANK([imei]),([imei] = [_THISROW].[imei]) ) ) -LIST([_THISROW]) )

Bisa juga ISBLANK() digunakan sebagai Valid-if statement:

ISBLANK( FILTER( "CalendarTable" AND( ([_THISROW].[Date] = [Date]), ([_THISROW].[Time] = [Time]) ) ) - LIST([_THISROW]) )

‍ISNOTBLANK()

Kebalikan dari ISBLANK() adalah ISNOTBLANK() atau NOT(ISBLANK()), dimana sering kita memiliki logika aplikasi untuk memunculkan column apabila suatu column lain telah terisi.  Contoh yang sederahana adalah apabila kita menginginkan suatu column hanya di tunjukkan kepada user apabila column yang lain memiliki value, misalnya untuk end-date dimana kita hanya menunjukkan column tersebut setelah column start-date di isi oleh user. Contoh:

ISNOTBLANK([Start-Date])

Tergantung bagaimana kita mempunyai cara kerja dan logika aplikasi apa yang akan kita terapkan, dan sering kali ISNOTBLANK() digunakan untuk melakukan filter awal agar jumlah data yang di analisa menjadi lebih sedikit sehingga aplikasi menjadi lebih cepat di running, seperti:

ISNOTBLANK( FILTER( "Tool List", ([Quantity At Storage) > 0) ) )

atau gunakan untuk sesuatu logika yang lebih complex seperti:

Dengan memberikan ISNOTBLANK() untuk SELECT() expression seperti:

COUNT(

SELECT(

Orders[_ROWNUMBER],

AND(

ISNOTBLANK([SpecialInstructions]),

([DateDone] >= [_THISROW].[BeginDate]),

([DateDone] < [_THISROW].[EndDate]))

)

)

akan mengurangi beban pencarian data karena kita sudah limit pencarian di tabel tersebut dengan ISNOTBLANK().

AND()

Beberapa contoh expression diatas sudah memberikan expression AND() ini, yang kita gunakan apabila kita menginginkan agar SELURUH item memberikan hasil TRUE. Baiknya kita gunakan bahasa yang sederhana untuk memahami expression yang akan kita buat, misalnya kita ingin agar Appsheet memberikan daftar dari semua item di suatu tabel dimana column special instruction itu ada value nya DAN tanggal selesai lebih besar dari pada tanggal mulai yang kita mau DAN JUGA tanggal selesai lebih kecil dari pada tanggal selesai yang kita specify. Ini adalah cara membaca expression diatas. Beberapa contoh penggunaan AND():

AND(FALSE, FALSE)

// FALSE

AND(FALSE, TRUE)

// FALSE

AND(TRUE, FALSE)

// FALSE

AND(TRUE, TRUE)

// TRUE

AND(

ISNOTBLANK([Grade]),

([Grade] = "A")

)

// TRUE apabila grade column memiliki value "A" dan tidak kosong.

AND(

([Name] = "Acme"),

([Rating] > 3),

[Preferred?]

)

// TRUE kalau rating>3 dan namanya adalah Acme dan column preferred itu yes.

AND(

(LEN([_THIS]) > 3),

CONTAINS([_THIS], "@")

)

// TRUE kalau column memiliki 3 huruf atau lebih, dan mengandung simbil @

AND(

IN(

[Sales Contact],

LIST(USEREMAIL(), "")

),//2

 NOT(

  [DO NOT CALL]

 ),//4

 ([Last Contact] < (TODAY() - 30))//3

)//1

//Validasi row tidak overalap:

AND(

([End] > [Start]),

(COUNT(

  FILTER(

    "MyTable",

    OR(

      AND(

        ([Start] >= [_THISROW].[Start]),

        ([Start] <= [_THISROW].[End])

      ),

      AND(

        ([End] >= [_THISROW].[Start]),

        ([End] <= [_THISROW].[End])

      )

      AND(

        ([Start] < [_THISROW].[Start]),

        ([End] > [_THISROW].[End])

      )

    )

  )

) = 0)

)

‍

AND([Color]="Green",[CompleteDate]>TODAY())

// Mencari warna hijau dengan completion date yang belum terlampaui.

IFS(

AND(

[_THISROW_BEFORE].[status_code]<>"Completed",

[status_code]="Completed"

), NOW(),

AND(

[_THISROW_BEFORE].[status_code]="Completed",

[status_code]<>"Completed"

), "",

AND(

[_THISROW_BEFORE].[status_code]="Completed",

[status_code]="Completed" ),

[_THISROW_BEFORE].[act_end_date])

//Contoh penggunaan AND() untuk memberikan value blank("") ataupun NOW()

OR()

OR() apabila digunakan untuk menjawab BENAR atau SALAH (TRUE / FALSE ) dalam suatu expression, akan memberikan hasil apabila ada salah satu pilihan yang memberikan nilai TRUE. Bisa dengan bahasa sehari-hari kita bayangkan suatu kalimat pilihan seperti apakah ada pilihan berikut ini yang benar? Kalau ada, maka tampilkan pilihan tersebut. Beberapa penggunaan seperti:

OR(FALSE, FALSE)

// FALSE

OR(FALSE, TRUE)

// TRUE

OR(TRUE, FALSE)

// TRUE

OR(TRUE, TRUE)

// TRUE

OR(([_THIS] < -2), ([_THIS] > 2))

// TRUE apabila column value berada di luar range -2 ke 2.

OR(([Color] = "Red"), ([Color] = "Yellow"), ([Color] = "Green"))

// TRUE kalau column value itu ada yang berwarna salah satu dari Red, Yellow, or Green.

OR() seperti diatas sama saja dengan:

IN([Color], {"Red" "Yellow", "Green"})

//Untuk memberikan access (misalnya di Show-if):

OR(

 AND((TIMENOW() >= "9:00 AM"), (TIMENOW() < "5:00 PM")),

 IN(WEEKDAY(TODAY()), {"Saturday", "Sunday"}),

 IN(

   USEREMAIL(),

   SELECT(Users[Email], [Special Access?], TRUE)

 )

)

Atau memberikan pilhan berdasarkan column:

OR(CONTAINS([Fruit],"Oranges"),CONTAINS([Fruit],"Apples"),CONTAINS([Fruit],"Bananas"))

‍

NOT()

Kadang kita ingin agar analisa data itu berdasarkan suatu yang TIDAK atau BUKAN kebernaran. Seperti misalnya BUKAN hari sabtu, atau BUKAN hari libur ataupun BELUM selesai.

Maka bisa digunakan NOT() expression seperti beberapa contoh berikut ini:

NOT(TRUE)

// FALSE

NOT(FALSE)

// TRUE

NOT(ISBLANK([Detail]))

// TRUE if the Detail column value is not blank.

Yang juga sama seperti:

ISNOTBLANK([Detail])

NOT(WEEKDAY(TODAY()) = "Sunday")

// TRUE apabila hari ini bukan hari Minggu.  

Bisa juga di tuliskan dengan:

WEEKDAY(TODAY()) <> "Sunday"

NOT(IN([Color]), {"Orange", "Red"})

// TRUE if the Color column value is not Orange or Red.

Bisa juga menjadi:

AND(([Color] <> "Orange"), ([Color] <> "Red"))

Sampai disini, kita mulai melihat bahwa beberapa expression bisa dituliskan berbeda-beda, malah bisa menggunakan expression lainnya tapi akan memberikan hasil yang sama. Ini akan sangat bergantung kepada pemahaman tentang logika aplikasi yang sedang dibuat oleh seorang App Creator. Dengan pengenalan tersebut, maka setiap aplikasi bisa dibuat dengan cara apa saja (banyak caranya) akan tetapi cara yang dipilih seharusnya memberikan hasil yang cepat, akurat dan mudah digunakan oleh user.

CONCATENATE()

CONCATENATE() merupakan TEXT atupun LONGTEXT yang kita gabung untuk memberikan informasi yang lebih kepada pengguna aplikasi. Contoh sederhana seperti menggabungkan Tag Number dengan Description dari item, atau menggabungkannama depan dengan nama belakang, ataupun untuk memastikan bahwa satu orang tidak boleh memilih item yang sama lebih dari satu kali, ataupun kita berusaha memberikan informasi yang singkat tapi jelas terkait dengan activity dan progress nya.

       CONCATENATE("Good", "morning", "!")

CONCATENATE("Good", " ", "morning", "!")

CONCATENATE("Good morning, ", [First Name], "!")

CONCATENATE([Last Name], ", ", [First Name])

CONCATENATE("Today is ", MONTH("4/1/2010"), "/", DAY("4/1/2010"), ".") : Today is 4/1.

CONCATENATE(

"Today is ",

INDEX(

   {"Sunday", "Monday", "Tuesday", "Wednesday",

     "Thursday", "Friday", "Saturday", "Sunday"},

   WEEKDAY("4/1/2010")

 ),

 "."

)

IF()

Sama seperti excel atau google sheet, IF(statement, True option, False Option), begitulah cara penulisannya dan bisa di buat multiple IF statement. Apabila kita mengerti IF di excel, maka akan lebih mudah memahaminya. Beberapa contoh penulisan expression IF() seperti:

 IF(TRUE, "Yes!", "No!")

// Yes!

IF(FALSE, "Yes!", "No!") // No!

IF((1 > 0), "Yes!", "No!") // Yes!

IF((1 < 0), "Yes!", "No!") // No!

IF(

ISBLANK(""),"Yes!",

"No!"

)

// Yes!

IF(

NOT(ISBLANK("")),"Yes!",

"No!"

)

// No!

// Equivalent to

IF(

ISNOTBLANK(""),"Yes!",

"No!"

)

IF(

([Count] < 0), 0,

[Count]

)

// Returns 0 kalau value count lebih kecil dari 0 dan kalau tidak value itu sendiri yang akan di berikan.

// Equivalent to:

MIN(

LIST(0, [Count])

)

IF(

([Date] = TODAY()), TRUE,

FALSE

)

// Returns TRUE kalau DATE value adalah hari ini, selain itu FALSE.

// Equivalent to:

([Date] = TODAY())

‍

IF(([Status] = "Open"), "Green",

IF(([Status] = "Closed"), "Red",

IF(([Status] = "Not Started"), "Blue",

"Purple")

)

)

//Returns "Green" kalau status OPEN, RED kalau status closed, atau blue kalau status belum dimulai, selain itu Purple.

‍

//USERSETTINGS IF:

IF(

USERSETTINGS("Names in uppercase?"), UPPER([Name]),

[Name]

)

// Returns NAME column sebagai Huruf besar kalau user setting true.

IF(

ISNOTBLANK([Phone Number]), [Phone Number],

"(no phone)"

)

      IF(

ISNOTBLANK([Customer].[Discount Rate]),

([Price] * [Customer].[Discount Rate]),

[Price]

)

//Leap Year?

IF(

 (MONTH(

     DATE(

       "2/28/"

       & YEAR(

        TODAY()

       )

     )

     + 1

   )

   = 2

 ),

 "leap year",

 "not leap year"

)

// Weekday or Weekend?

IF(

 IN(

   WEEKDAY(TODAY()),

   LIST(1, 7)

 ),

 "It's the weekend!",

 "It's a weekday."

)

‍

‍

IFS()

IFS() merupakan salah satu logic expression yang hampir selalu ada apabila Hart Exinity membuat aplikasi. IFS() akan mengecek kondisi yang diberikan secara urutan dari atas kebawah dan akan berhenti mengecek apabila ditemui kondisi yang menghasilkan TRUE. Jadi, kita harus mengurutkan kondisi-kondisi sedemikian rupa sehingga kondisi yang paling tidak bawah adalah kondisi yang terakhir. Contoh untuk menentukan kondisi SUBMITTED, REVIEWED, atau APPROVED.

Beberapa contoh penulisan IFS() adalah:

IFS(TRUE, "Yes!") // Yes!

IFS(FALSE, "Yes!") // blank (no value)

IFS(FALSE, "Yes!", TRUE, "No!") // No! (equivalent to

IF(FALSE, "Yes!", "No!")

//Contoh penulisan untuk pemilihan nomor telphone:

IFS(

 ISNOTBLANK([Mobile Phone]), [Mobile Phone],

 ISNOTBLANK([Work Phone]), [Work Phone],

 ISNOTBLANK([Home Phone]), [Home Phone]

)

//Equivalent to:

ANY(

 LIST(

   [Mobile Phone]

   [Work Phone],

   [Home Phone]

 )

 - LIST("")

)

//Summarize Delivery

IFS(

 ISBLANK([Due Date]),

   "Unscheduled",

 (TODAY() > [Due Date]),

   "Overdue!",

 (TODAY() = [Due Date]),

   "Due today",

 (TODAY() = ([Due Date] - 1)),

   "Due tomorrow",

 TRUE,

   (([Due Date] - TODAY()) & " days remain")

)

//Convert a weekday number to a name.

IFS(

 (WEEKDAY(TODAY()) = 1), "Sunday",

 (WEEKDAY(TODAY()) = 2), "Monday",

 (WEEKDAY(TODAY()) = 3), "Tuesday",

 (WEEKDAY(TODAY()) = 4), "Wednesday",

 (WEEKDAY(TODAY()) = 5), "Thursday",

 (WEEKDAY(TODAY()) = 6), "Friday",

 TRUE, "Saturday",

)

//Functionally equivalent to but less efficient than:

SWITCH(

 WEEKDAY(TODAY()),

 1, "Sunday",

 2, "Monday",

 3, "Tuesday",

 4, "Wednesday",

 5, "Thursday",

 6, "Friday",

 "Saturday",

)

***************

Contoh secara logika kita menginginkan:

If Speed > 70 then = Speed - 30

If Speed is lower than 70 but higher than 30 then = Speed -15

If Speed lower than 30 then = Speed, maka penulisannya menjadi:

IF(

  [Speed] > 70 , [Speed] - 30,

  IF(

     [Speed] < 30 , [Speed] ,

     [Speed] - 15

     )

)

//OR:

IFS(

([Speed] > 70),

  ([Speed] - 30),

([Speed] > 30),

  ([Speed] - 15),

TRUE,

  [Speed]

)

‍SWITCH()

SWICH() merupakan bentuk lain dari logika expression dengan menganalisa semua kemungkinan dan memberikan default value kalau tidak ada yang memberikan nilai TRUE. Contoh penulisan expression seperti:

SWITCH(

 WEEKDAY(TODAY()),

 1, "Sunday",

 2, "Monday",

 3, "Tuesday",

 4, "Wednesday",

 5, "Thursday",

 6, "Friday",

 "Saturday",

)

‍

‍

‍

HERE()

HERE() akan memberikan LATLONG position sesuai dengan device user apabila GPS di hidupkan. Apabila GPS dimatikan, maka akan muncul nilai 0000,0000. Beberapa penulisan HERE() untuk initial value tinggal di tuliskan HERE(), contoh lainnya adalah:

DISTANCE([HERE(), [LatLong_Column]) <= 1

//Sample:

DISTANCE(HERE(), LATLONG(13.993581, -40.555116))

// Apabila digabung dengan CONTECT():

OR(

CONTEXT(“Host”) <> “Browser”, //1)

IF(

IN(

USEREMAIL(),

LIST(“Email1”, “Email2”)

), true,

DISTANCE(

HERE(), LatLong(“41.945583”, “-43.812112”)

) <= 1

) //2)

)

Atau menggunakan DISTANCE untuk membatasi action user berdasarkan jarak dengan suatu koordinate yang di tentukan:

DISTANCE (LATLONG (20.700896, -103.364275) , HERE ()) <.2

‍

CONTEX()

CONTEXT() dapat di analogikan sebagai suatu expression yang akan memberikan indikasi tentang penggunaan aplikasi. Seperti user saat ini berada di view apa? dan kalau user sedang mengisi form maka sembunyikan beberapa column berikut atau sebaliknya, munculkan column hanyaapabila user berada di form view.

Untuk hide column di semua view kecuali form, maka gunakan expression berikut di Show_If :

CONTEXT("ViewType") = "form"

Sebaliknya, untuk di hide:

CONTEXT("ViewType") <> "form"

Untuk di tunjukkan hanya di form view dan detail view:

IN(CONTEXT("View"), LIST("MyTable_Form", "MyTable_Detail"))

Beberapa contoh lain:

IN(CONTEXT("View"), LIST("Orders_Detail", "Orders_Form"))

("Form" = CONTEXT("ViewType"))

SWITCH(CONTEXT("Host"), "Device", "Tap below to:", "Browser", "Click below to:", "")

Untuk menggunakan CONTEXT() sebagai identifikasi user device, bisa gunakan ini di intial value:

CONTEXT("Device")

‍

LINKTOVIEW()

LINKTOVIEW() merupakan action type expression yang di gunakan untuk membawa user ke view yang kita inginkan apabila user melakukan click untuk action tersebut.

Contoh:

LINKTOVIEW("My Open Action")

LINKTOVIEW(IF([Choice] = "Veggie View", "Vegetables", "Fruits"))

‍

LINKTOFORM()

LINKTOFORM() akan membawa user kapada form yang di inginkan dan mengisikan dengan beberapa data yang kita specify.

LINKTOFORM("Table2 Form", "Name2", [Name1], "Color2", [Color1])

‍

LINKTOFILTEREDVIEW()

Apabila kita ingin membawa user kepada suatu view berdasarkan kepada suatu filter criteria, maka gunakan LINKTOFILTEREDVIEW():

LINKTOFILTEREDVIEW("Some Table View", [Date Column] = TODAY())

LINKTOFILTEREDVIEW(“Order View”, [Order Status] = Open)

‍

LINKTOFILTEREDVIEW(

 "Table A",

 (

   COUNT([meso] - LIST())

   <> COUNT([meso] - [_THISROW].[meso])

 )

)

/*

‍

LINKTOFILTEREDVIEW(“Customers Table View”,

AND([Sales Rep] = [_THISROW].[Sales Rep Id], [Priority] = High))

‍

LINKTOFILTEREDVIEW(“Students Table View”, [Age] < [_THISROW].[Age])

‍

LINKTOFILTEREDVIEW("Inventory list_Detail", [Description] = [_THISROW].[Scanner])

‍

LINKTOFILTEREDVIEW(“AllProperties_Results”,

IN([Neighborhood_name], [_THISROW].[Neighborhood_name]))

‍

LINKTOFILTEREDVIEW("Inventory",

IN([Product Code], LIST(“100001”,“100002”, "100003")))

CONCATENATE(

LINKTOFILTEREDVIEW(

"Inventory",

IN([Product Code], LIST(“100001”,“100002”))

),

"&quickedit=true"

)

‍

LINKTOFILTEREDVIEW(“Student Names”, [Program Name]=[_THISROW].[Program Name])

‍

LINKTOFILTEREDVIEW("Items", AND([Color] = [_THISROW].[Color], [Size] = [_THISROW].[Size]))

‍

[_THIS]

Expression [_THIS] bisa diartikan sebagai cell ini. kita bisa gabungkan [_THIS] dengan ISBLANK() menjadi ISBLANK{[_THIS]) untuk melakukan analisa apabila cell tersebut blank atau tidak.

Dimulai di sini, akan semakin sulit untuk mencerana dan yang dibutuhkan hanyalah latihan -latihan dan latihan. 10 expression berikutnya lebih merupakan suatu cara bagi app kreator untuk memberikan kepada user apa yang seharusnya user lihat/butuhkan. Apabila kita tidak mengetahui keinginan user, maka biasanya di lakukan sambil data di kumpulkan dan mulai di susun setelah itu. Akan lebih baik apabila sudah paham mengenai bahasa SQL, karena hampir sama dengan itu - Appsheet's way.

[_THISROW]

Expression [_THISROW] akan banyak di jumpai apabila kita memiliki lebih dari satu tabel. [_THISROW] bisa saja di artikan sebagai ROW INI, tapi yang akan di berikan adalah KEY value. Sehingga [_THISROW] akan mendekati apabila di gabungkan dengan expression lain dan tidak berdiri sendiri. Expression ini yang paling singkat akan merupakan list /daftar dan bisa dengan mudah dibuat dengan LIST([_THISROW].

SELECT()

SELECT() expression merupakan inti dari Appsheet. Ada beberapa fungsi yang sama tapi pada dasarnya merupakan keturunan atau kependekan (makro) dari SELECT(). Untuk memahami kita harus membayangkan bahwa kita ada di tabel terpisah dan ingin agar tabel laindi tunjukkan di tabel terpisah tersebut dengan kriteria yang kita inginkan. Disinikami berikan contoh sederhana saja:

SELECT(Customers[Phone Number], ([Provinsi] = "Bali"))

// akan memberikan daftar dari nomor telephone dari Bali. Duplicate nomor telephone mungkin akanjuga di berikan apabila ada.

SELECT(Customers[Provinsi], ([Kecamatan] = "Cakung"), TRUE)

// memberikan daftar provinsi yang memiliki kecamatan dengan nama CAKUNG. TRUE menunjukkan untuk menghilangkan duplicate provinsi apabila ada.

ANY()

ANY() merupakan pasangan dari SELECT() yang akan memberikan salah satu dari item yang berada di daftar:

ANY(Students[Name])

ANY(SELECT(Students[Name], TRUE))

ANY(LIST(1, 2, 3)) //returns Number: 1

ANY({"Red", "Yellow", "Green"}) //returns Text: Red

ANY(SELECT(Products[Price], ([Color] = "Orange")))

//Equivalent to:

LOOKUP("Orange", "Products", "Color", "Price")

ANY(TOP(SORT(Products[Price], TRUE), 1))

//Equivalent to

MAX(Products[Price])

Preferred Value

ANY(

TOP(

(

LIST([Mobile Phone], [Office Phone], [Home Phone])

- LIST("")

),

1

)

)

//Equivalent to:

INDEX(

(

LIST([Mobile Phone], [Office Phone], [Home Phone])

- LIST("")

),

1

)

‍

‍

SUM()

        SUM(Classes[Seat Count])

SUM(SELECT(Classes[Seat Count], TRUE))

SUM([Discounts]) // The sum of the items in the Discounts column value, where Discounts is of type List.

SUM(LIST(1, 2, 3)) //returns Number: 6

Sum Values from Select Rows

SUM(

 SELECT(

   Deliveries[DeliveryCharge],

   AND(

     ([DateDone] >= [_THISROW].[BeginDate]),

     ([DateDone] < [_THISROW].[EndDate])

   )

 )

)

LOOKUP()

        LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone")

//Equivalent to

ANY(SELECT(Restaurants[Phone], ([Name] = "Bob's Burgers")))

LOOKUP([_THISROW], "Managers", "Location", "Name")

//Equivalent to

ANY(SELECT(Managers[Name], ([Location] = [_THISROW])))

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date"))

//Equivalent to

ANY(SELECT(Orders[Order Date], ([Order ID] = [_THISROW].[Order ID])))

//or

[Order ID].[Order Date].

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

The equivalent SELECT() expression:

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

‍

‍

COUNT()

COUNT() sudah cukup jelas dengan adanya SELECT().

FILTER()

FILTER() merupakan special case SELECT() dimana yang diberikan adalah key value. FILTER() dengan IN() akan memberikan hasil yang efficient dan lebih cepat.

FILTER("Customers", ([Provinsi] = "Bali"))

FILTER(Customers, ([Kecamatan] = "Cakung"))

‍

IN()

IN() sudah cukup jelas dan tinggal di mantapkan saja.

EOMONTH()

EOMONTH() biasanya digunakan untuk membatasi bulan ke bulan yang lainnya dalam kalender. Contoh ada di bagian bawah dari tulisan ini.

REF_ROWS()

Ini adalah expression yang singkat dan biasanya dibuat secara otomatis apabila kita menyatakan suatu column adalah type REF. Akan tetapi, penggunaan harus juga di perhatikan karena dapat memperlambat sytem sync time.

‍

Ada banyak expression lain yang lebih tidak sering di gunakan akan tetapi sangat membantu. Silahkan gunakan contoh-contoh expression dibawah ini sebagai referensi apabila kita mempunyai logika aplikasi yang benar-benar unik dan tidak memiliki contoh yangkami kumpulkan dari beragam sumber, sebagian besar merupakan koleksi dari help article Appsheet dan tidak kami terjemahkan kedalam bahasa Indonesia, jadi apabila ingin melihat bagaimana contoh-conthoh expression dituliskan, silahkan menggunakan CONTROL+F untuk mencari di halaman ini:

List Expressions

LIST(any-value[, another-value]...)

{ any-constant[, another-constant]... }

table-name[column-name]

any-list + another-list

any-list - another-list

ANY(any-list)

AVERAGE(numeric-list)

COUNT(any-list)

FILTER(table-name, include-row?)

IN(match-value, in-list)

INTERSECT(list-1, list-2)

INDEX(any-list, number)

LOOKUP(match-value, in-table, in-column, return-column)

MAX(numeric-list)

MAXROW(in-table, by-column, include-row?)

MIN(numeric-list)

MINROW(in-table, by-column, include-row?)

ORDERBY(ref-list, column-name[, descending?[, another-column-name]]...)

SELECT(table-column, include-row?[, only-distinct?])

SORT(any-list[, descending?])

STDEVP(this-list)

SUM(numeric-list)

TOP(any-list, number)

UNIQUE(any-list)

Membuat LIST

{1, 2, 3}

{"Apple", "Banana", "Cherry"}

LIST(1, 2, 3)

{1, 2, 3}

LIST("Apple", "Banana", "Cherry")

{"Apple", "Banana", "Cherry"}

LIST([HomePhone], [CellPhone], [WorkPhone])

Customers[Phone Number]

Menambahkan LIST

LIST+

Customers[PhoneNumber] + {"1800-444-4444", "1800-555-5555"}

Mengurangi LIST

LIST-

Customers[PhoneNumber] - {"1800-444-4444", "1800-555-5555"}

ANY(any-list)

ANY() // picks a single arbitrary value from a list of values.

ANY({1, 2, 3})// returns either 1, 2, or 3.

ANY(Item[Quantity])

ANY(SELECT(Customers[Phone], CONTAINS([Name], "John Doe"))

AVERAGE(numeric-list)

AVERAGE() //returns the average value of the items in the list.

AVERAGE({1, 2, 3}) // returns 2

AVERAGE({1, 2, 3, 4}) // returns 2.5

AVERAGE(Item[Quantity])

COUNT(any-list)

COUNT() //return the count of items in the list.

COUNT({1, 2, 3}) // returns 3

COUNT(LIST("Apple", "Banana", "Cherry"))

COUNT({"Apple", "Banana", "Cherry"})

COUNT(Customers[Phone Number])

COUNT(SELECT(Customers[Phone Number], ([State] = "WA")), TRUE)

IN(match-value, in-list)

IN() //checks for the presence of a value in a list.

IN([ColumnName], {1, 2, 3})

IN([ColumnName], LIST((1 * 1), (2 * 2), (3 * 3)))

IN("Banana", [FruitEnumList])

OR(IN("Apple", [FruitEnumList]), IN("Banana", [FruitEnumList]))

AND(IN("Apple", [FruitEnumList]), IN("Banana", [FruitEnumList]))

AND(IN("Apple", [FruitEnumList]), NOT(IN("Banana", [FruitEnumList])))

INTERSECT(list-1, list-2)

INTERSECT()

INTERSECT([List of Odd Numbers], [List of Square Numbers])

INDEX(any-list, number)

INDEX() //picks a single specific value from a list of values.

INDEX({41, 27, 388}, 2)

INDEX(Item[Quantity], 2)

INDEX(SELECT(Customers[Phone], CONTAINS([Name], "John Doe")), 1)

LOOKUP(match-value, table-name, column-name, return-column)

//LOOKUP() is similar to the VLOOKUP() spreadsheet function.

LOOKUP("John Doe", Customers, Name, Phone)

ANY(SELECT(Customers[Phone], [Name] = "John Doe"))

MAX(numeric-list)

MAX() returns the maximum item in the list.

MAX({1, 2, 3}) // returns 3

MAX({3.1, 4.2, 9.3, 15.4, 32.5})

MAX(Item[Price])

MAXROW(of-table, by-column[, include-row?])

MAXROW() //returns a reference to the row having the largest value in a column.

MAXROW(Customers, SignupDate) // returns the most recently created customer.

MAXROW(Customers, SignupDate, ([State] = "WA"))

MAXROW(Meeting, "Date")

MIN(numeric-list)

MIN() //returns the minimum item in the list.

MIN({1, 2, 3}) // returns 1.

MIN({3, 4, 9, 15, 32}) // returns 3

MIN(Item[Price]) // returns the minimum value in the Price column of the Item table.

MINROW(of-table, by-column[, include-row?])

MINROW() //returns a reference to the row having the smallest value in a column

MINROW(Customers, SignupDate) //returns the least recently created customer.

MINROW(Customers, SignupDate, ([State] = "WA")) // returns the least recently created customer from WA state.

SORT(any-list[, descending?])

SORT()

SORT({22, 44, 33}) // returns the list {22, 33, 44}

SORT({22, 44, 33}, TRUE) // returns the list {44, 33, 22}

SPLIT(text, separator)

SPLIT() // returns a list of fragments of text between separator. For example,

SPLIT("Red:Yellow:Green", ":") //returns a list containing the values Red, Yellow, and Green.

SUM(numeric-list)

SUM() //returns the sum of the items in the list.

SUM({1, 2, 3}) // returns 6.

SUM({3, 4, 9, 15, 32}) // returns 63.

SUM(Item[Quantity]) // returns the sum of the values in the Quantity column of the Item table.

SUM([Order Details][Total])

TOP(any-list, number)

TOP() //returns a list of the initial number of items for any-list.

TOP({1, 2, 3, 4, 5}, 3) //returns a list containing the values 1, 2, and 3.

TOP(LIST("John", "Mary"), 4)

TOP(SORT(Customers[Age]), 0) //returns a list with 0 items.

TOP(

ORDERBY(

FILTER(

Purchases,

([Customer Name] = "Alice")

),

Purchase Date,

TRUE

),

3

)

UNIQUE(any-list)

UNIQUE() //returns the unique items from the items in the list.

UNIQUE({1, 2, 2, 3, 3}) // returns {1, 2, 3}

UNIQUE({"a", "b", "c", "c", "c"}) // returns {"a", "b", "c"}

UNIQUE(Item[Type]) // returns the unique list of values from the Type column of the Item table.

Using a List in a Column Constraint

/*Lists are often used with column constraints such as:*/

Editable_If, Show_If, or Valid_If

//In column constraints:

[_THIS] //refers to the current column value.

[_THISROW] //refers to the current row value.

IN([_THIS], {1, 2, 3}) // returns TRUE if the value in the column value is contained in the list.

{1, 2, 3} // is a short form of the previous expression. It's equivalent to

IN([_THIS], {1, 2, 3})

IN(USEREMAIL(), TableName[Emails])

COUNT(SELECT(Customers[Phone Number], [State] = [_THIS])) > 100

COUNT(FILTER(Customers, [State] = [_THISROW].[State])) > 1000

COUNT(SELECT(Customers[Name], AND([Name] = [_THIS], [Name]<> [_THISROW].[Name]))) = 0

Preventing Duplicate Field Values

NOT(

IN([_THIS],

SELECT(Customers[State],

NOT(

IN([CustomerId],

LIST([_THISROW].[CustomerId])

)

)

)

)

)

/Similar for duplicate prevention expressions:

ISBLANK(

FILTER("MyTable",

AND(ISNOTBLANK([MyColumn]),

([MyColumn] = [_THISROW].[MyColumn])

)

)

- LIST([_THISROW])

)

Sorting a List of Keys with ORDERBY()

ORDERBY(ref-list, column-name[, descending?[, another-column-name]]...)

ORDERBY(

[Related Orders], [Customer Name],

[Order Date],

FALSE

)

**********

Constructing a List from the Column of a Table

Fruits[Name]

equivalent to

SELECT(Fruits[name], TRUE, FALSE)

Orders[Customer]

Equivalent to

SELECT(Orders[Customer], TRUE, FALSE).

Order Details[SKU] //produces a list of all SKU column values in the Order Details table.

Equivalent to

SELECT(Order Details[SKU], TRUE, FALSE)

Constructing a List from a Column of Type List or EnumList

//The value of a column of type List or EnumList is already a list, and may be used directly anywhere a list is allowed within an expression.

[Vacation Dates]

[Notification Emails] //gives the list of notification email addresses from the current row, e.g. in an Events table.

[Related Order Details]

//A table-column reference to a column of type List or EnumList will produce a list of lists.

To "flatten" the list-of-lists into a single list composed of the values of the component lists, wrap the table-column reference with SPLIT().

SPLIT(Employees[Vacation Dates], " , ") //produces a list of all employee vacation dates.

SPLIT(Events[Notification Emails], " , ") //produces a list of all notification email addresses.

‍

Constructing a List from Expressions

//The LIST() function constructs a list from any combination of raw values, column values, and expressions.

The values in the resulting list will be in the order they were given to the LIST() function.

LIST("Apple", "Banana", "Cherry") //produces a list of Text values: Apple, Banana, Cherry.

LIST([Mobile Phone], [Office Phone], [Home Phone]) //produces a list of phone numbers from three column values of the current row.

LIST(1, (1 + 1), (6 / 2), ROUND(POWER(2, 2)), ROUND(SQRT(25))) //produces a list of Number values: 1, 2, 3, 4, 5.

LIST() //produces an empty list.

‍

Constructing a List from a List Dereference

[Related Orders][Order Date]

SELECT(

 Orders[Order Date],

 IN([Order ID], [_THISROW].[Related Orders])

)

Constructing a List from Raw Values

{ "Apple", "Banana", "Cherry" } //produces a list of Text values: Apple, Banana, Cherry.

{ 1, 2, 3 } //produces a list of Number values: 1, 2, 3.

{ [Mobile Phone], [Office Phone], [Home Phone] } //produces a list of Text values:

{ 1, (1 + 1), (6 / 2), ROUND(POWER(2, 2)), ROUND(SQRT(25)) }

{ "(1 + 1)", "(6 / 2)" }

Constructing a List with Addition

({ 1, 2, 3 } + LIST(2, 3, 4))

(Employees[Office Phone] + Employees[Home Phone])

({ 3 } + { 3.14 })

(LIST() + { 3.14 })

Constructing a List with Subtraction

({ 1, 2, 3 } - LIST(2, 3, 4)) //produces a list of one Number value: 1

({ "Bob", "Mary", "Bob", "Alice" } - { "Alice" })

({ "Bob", "Mary", "Bob", "Alice" } - LIST())

Equivalent to

UNIQUE({ "Bob", "Mary", "Bob", "Alice" })

({ "Bob", "Mary", "Bob", "Alice" } - { "Bob" })

Using Lists

Using Lists in Functions

A variety of functions accept lists as input:

ANY() - One arbitrary item of a list.

AVERAGE() - Arithmetic average of list of numeric values.

COUNT() - Count of list items.

IN() - Is item in a list?

INDEX() - One specific item of a list.

INTERSECT() - List of items common to two lists.

ISBLANK() - Does the list have no items?

ISNOTBLANK() - Does the list have any items?

MAX() - Highest of a list of numeric or temporal values.

MIN() - Lowest  of a list of numeric or temporal values.

ORDERBY() - List of rows in custom order.

SELECT() - List of column values from select rows.

SORT() - List of items in order.

STDEVP() - Arithmetic standard deviation of a list of numeric values.

SUM() - Arithmetic sum of a list of numeric values.

TOP() - List of initial items of list.

UNIQUE() - List of items with duplicates removed.

Using Lists to Show and Hide Columns

IN(USEREMAIL(), SELECT(Users[Email], ("Admin" = [Role])))

IN(CONTEXT("ViewType"), { "deck", "table" })

Using Lists to Show and Hide Views

IN(USEREMAIL(), SELECT(Users[Email], ("Admin" = [Role])))

‍

Using Lists to Suggest Column Values

Customers[Name]

LIST(TODAY(), (TODAY() + 1), (TODAY() + 2)

Using Lists to Validate Column Values

Customers[Name]

{ "Ordered", "Prepared", "Shipped", "Delivered" }

Constructing a List from a Function

//A number of functions produce lists as their result:

EXTRACT() - List of recognizable elements from textual value.

EXTRACTDATES() - List of dates from textual value.

EXTRACTDATETIMES() - List of date-times from textual value.

EXTRACTDURATIONS() - List of durations from textual value.

EXTRACTEMAILS() - List of email addresses from textual value.

EXTRACTHASHTAGS() - List of hashtags from textual value.

EXTRACTMENTIONS() - List of mentions from textual value.

EXTRACTNUMBERS() - List of numeric values from textual value.

EXTRACTPHONENUMBERS() - List of phone numbers from textual value.

EXTRACTPRICES() - List of prices from textual value.

EXTRACTTIMES() - List of times from textual value.

FILTER() - List of select rows in table.

GCAL_MEETINGS_AT()

GCAL_MEETINGS_FROM()

GCAL_MEETINGS_WITH()

INTERSECT() - List of items common to two lists.

LIST() - New list of values.

ORDERBY() - List of rows in custom order.

REF_ROWS() - List of rows referencing this row.

SORT() - List of items in order.

SPLIT() - List of elements from textual value.

TOP() - List of initial items of list.

UNIQUE() - List of items with duplicates removed.

Constructing a List from Expressions

LIST("Apple", "Banana", "Cherry")

LIST([Mobile Phone], [Office Phone], [Home Phone])

LIST(1, (1 + 1), (6 / 2), ROUND(POWER(2, 2)), ROUND(SQRT(25)))

LIST() produces an empty list.

Constructing a List from Raw Values

{ "Apple", "Banana", "Cherry" } //produces a list of Text values: Apple, Banana, Cherry.

{ 1, 2, 3 } //produces a list of Number values: 1, 2, 3.

{ [Mobile Phone], [Office Phone], [Home Phone] }

{ 1, (1 + 1), (6 / 2), ROUND(POWER(2, 2)), ROUND(SQRT(25)) }

Constructing a List with Addition

({ 1, 2, 3 } + LIST(2, 3, 4))

(Employees[Office Phone] + Employees[Home Phone])

({ 3 } + { 3.14 })

(LIST() + { 3.14 })

Constructing a List with Subtraction

({ 1, 2, 3 } - LIST(2, 3, 4)) //produces a list of one Number value: 1

({ "Bob", "Mary", "Bob", "Alice" } - { "Alice" })

({ "Bob", "Mary", "Bob", "Alice" } - LIST())

‍

Date and Time Expressions

Current Date & Time

NOW() //for the current DateTime on the user's device.

TODAY() //for the current Date on the user's device.

//Equivalent to

DATE(NOW())

TIMENOW() //for the current Time on the user's device.

//Equivalent to

TIME(NOW())

UTCNOW() //for the current DateTime with no timezone offset (i.e., UTC time).

‍

Time Components

HOUR() //for the hour component of a specific Duration.

MINUTE() //for the minute component of a specific Duration.

SECOND() //for the second component of a specific Duration.

//Note that each of HOUR(), MINUTE(), and SECOND() accept as input a Duration value,

//not a Time value. To convert a Time value to a Duration value, subtract another Time value.

//For instance, to convert the current time-of-day to a Duration:

TIMENOW() - "00:00:00"

Date Components

DAY() //for the day of the month from a Date.

EOMONTH()

MONTH() //for the month number from a Date.

YEAR() //for the year from a Date.

WEEKDAY() //for the day number from a Date. Sunday is 1, Saturday is 7.

WEEKNUM()

WORKDAY()

Formatting Dates and Times as text

TEXT() //accepts a DateTime, Date, or Time and a format string, and returns a text representation.

Examples that Compute Dates

TODAY() + 1 // adds one day (a Number) to the current Date.

TODAY() - 3 // subtracts 3 days (a Number) from the current Date.

[StartDate] + 7 // adds 7 days (a Number) to the StartDate (a Date).

TODAY() - (WEEKDAY(TODAY()) - 1)  // the date of the preceding Sunday.

TODAY() - (WEEKDAY(TODAY()) - 2)  // the date of the preceding Monday.

Examples that Compute Times

TIMENOW() + 1 // adds one hour (a Number) to the current Time.

TIMENOW() + "003:03:00" // adds 3 hours 3 minutes (a Duration) to the current Time.

TIMENOW() - "003:03:00" // subtracts 3 hours 3 minutes (a Duration) from the current Time.

Examples that Compute DateTimes

NOW() + 1 // adds one day (a Number) to the current DateTime.

NOW() - 3 // subtracts three days (a Number) from the current DateTime.

[TargetDateTime] + "012:59:00" // adds 12 hours 59 minutes (a Duration) to a DateTime value.

[TargetDate] + ([TargetTime] - "00:00:00")  // Creates a DateTime value from a Date value and a Time value.

Examples that Compute Durations

TIMENOW() - "12:30:00" // the Duration between the current Time and 12:30 PM (a Time).

NOW() - "03:15:30" // the Duration between the current Time and 3:15:30 AM (a Time) on December 30, 1899 (the default Date if none is included).

TODAY() - "12/30/2001" // the Duration between the current Date and December 30, 2001 (a Date).

[EndDate] - [StartDate] // the Duration between StartDate (a Date) at midnight and EndDate (a Date) at midnight.

IF(

([StopWhen] > [StartWhen]), ([StopWhen] - [StartWhen]),

(([StopWhen] + 24) - [StartWhen])

)

// the Duration between StartWhen and StopWhen (two DateTime values).

Examples that Compute Durations in Days, Months, or Years

HOUR(TODAY() - [TargetDate]) / 24

// number of days between today's date and the Date value given in the TargetDate column.

FLOOR(

(

(HOUR([EndDate] - [StartDate]))

-

(([Years] * 365.24 * 24.0) + ([Months] * 30.4 * 24.0))

)

/ 24.0

)  

// the number of days between the start and end dates.

//It assumes virtual columns Years and Months contain the number of years and months, respectively, computed using the formulas above.

FLOOR(

(

(HOUR([EndDate] - [StartDate]))

-

([Years] * 365.25 * 24.0)

)

/

(30.4 * 24.0)

)

// the number of months between the start and end dates. It assumes virtual column Years contains the number of years computed using the formula above.

FLOOR(

(HOUR([EndDate] - [StartDate]))

/

(365.24 * 24.0)

)

// the number of years between the start and end date.

Examples that Compare Dates, Times, and DateTimes

(TODAY() - [When]) = 7 // a Yes/No value indicating whether the Date or DateTime value of the When column value is exactly seven days before today's Date.

([When] - TODAY()) = 7 // a Yes/No value indicating whether the Date or DateTime value of the When column value is exactly seven days after today's Date.

AND([OrderDateTime] >= [StartDateTime], [OrderDateTime] <= [EndDateTime])

AND([OrderDate] >= [StartDate], [OrderDate] <= [EndDate])

AND(([When] >= (TODAY() - 7)), ([When] <= TODAY()))

TODAY() > ([TargetWhen] + 1) // a Yes/No value indicating whether the Date or DateTime value of the TargetWhen column is more than a day in the past.

[Timestamp] > (NOW() - 1) // a Yes/No value indicating whether the DateTime value of the Timestamp column is within 24 hours of the current date & time.

[OrderDateTime] >= (NOW() - "001:30:00")  //a Yes/No value indicating whether the DateTime value of the OrderDateTime column is within 1 hour 30 minutes of the current DateTime.

IN(MINUTE([_THIS] - "00:00:00"), LIST(0, 15, 30, 45)) // a Yes/No value indicating whether the minute component of the DateTime or Time value of the current column falls on the quarter hour (i.e., is 0, 15, 30, or 45).

//Note that when dates are used as constant values within an expression, they must be represented in the MM/DD/YYYY format. This doesn't mean your spreadsheet data must use dates in this format: the date representation in your spreadsheets is determined by the locale/language of the spreadsheet.

**********

Text Expressions

CONCATENATE(text-expression1[, text-expression2]...) //combines two or more text values.

CONTAINS(text, keyword) //returns TRUE if keyword is found in text.

FIND(fragment, text) //returns the position at which fragment first occurs within text, case-sensitive.

INITIALS(name) //returns the first letter of each component of name. For example, INITIALS("Bruce Lee") returns "BL".

ISBLANK(expression) //tests if expression is empty.

ISNOTBLANK(expression) //tests if expression is not empty.

LEFT(text, number) //returns the left-most number of characters from text.

LEN(text-expression) //returns the length of text-expression.

LOWER(text-expression) //returns text-expression in lowercase.

RIGHT(text, number) //returns the right-most number of characters from text.

SPLIT(text, separator) //returns a list of fragments of text between separator.

//For example:

SPLIT("Red:Yellow:Green", ":") //returns a list containing the values Red, Yellow, and Green.

SUBSTITUTE(original-text, text-to-replace, replace-with) //returns original-text with all occurrences of text-to-replace replaced with replace-with.

TRIM(text) //removes spaces from the beginning and end of the string, and removes duplicate spaces from the interior of the string.

UNIQUEID() //returns a unique Text value suitable for use as a key.

UPPER(text-expression) //returns text-expression in uppercase.

USEREMAIL() //returns the email address of the current user.

USERNAME() //returns the name of the current user, if known.

**********

Other Expressions:

CONCATENATE(text-expression1[, text-expression2]...) //combines two or more text values.

CONTAINS(text, keyword) //returns TRUE if keyword is found in text.

CONTEXT(option) //returns a Text value that indicates where in the app this expression is being used. It's useful for "meta-data" operations and currently has very limited functionality. There are currently only three allowed options:

CONTEXT("Host") //returns one of the following: "Browser", "Device", or "Server" to indicate where the expression is being evaluated.

CONTEXT("View") //returns the name of the current view or null if the expression is not run in the context of a view.

CONTEXT("ViewType") //returns the type of the current view (table, chart, map, etc) or null if the expression is not run in the context of a view.

DISTANCE(location, location) //returns the approximate distance in kilometers between two locations expressed as LatLong values. DISTANCE() does not work with Address values.

HERE() //returns the current LatLong of the user.

IF(condition, then-expression, else-expression) //returns either then-expression or else-expression, depending on condition. Both then-expression and else-expression must be of the same type (e.g., text, number, etc).

FIND(fragment, text) //returns the position at which fragment first occurs within text, case-sensitive.

LEFT(text, number) //returns the left-most number of characters from text.

LEN(text-expression) //returns the length of text-expression.

HYPERLINK(address, text) //returns a hyperlink to address with the clickable text. For example, the formula:

HYPERLINK("http://en.wikipedia.org", "Click here to visit Wikipedia") //will create a link that looks like this:

Click here to visit Wikipedia.

//If you use the HYPERLINK() formula in a non-virtual column, make sure to mark IsHyperLink as TRUE in the Type Qualifier to ensure the value of this column is written as HYPERLINK() formula in the spreadsheet.

INDEX(list, number) //returns the value of item number in list.

INITIALS(name) //returns the first letter of each component of name. For example,

INITIALS("Bruce Lee") returns "BL".

LAT(LatLong) //returns the latitude component of a LatLong value

LATLONG(lat, long) //returns a LatLong value from component latitude and longitude (both Decimal values)

LINKTEXT(hyperlink) //returns the link text of hyperlink. For example, calling

LINKTEXT(HYPERLINK("http://google.com", "Google")) will return "Google".

LINKURL(hyperlink) //returns the URL part of hyperlink. For example, calling

LINKURL(HYPERLINK("http://google.com", "Google")) will return "http://google.com".

LONG(LatLong) //returns the longitude component of a LatLong value

RIGHT(text, number) //returns the right-most number of characters from text.

SUBSTITUTE(original-text, text-to-replace, replace-with) //returns original-text with all occurrences of text-to-replace replaced with replace-with.

TEXT_ICON(text) //returns a Thumbnail image containing text. This is particularly useful in constructing images from text data for better presentation in gallery or deck views. A common use case is, e.g.,

TEXT_ICON(INITIALS([Student Name])).

UNIQUEID() //returns a unique Text value suitable for use as a key.

USEREMAIL() //returns the Email of the current user.

USERLOCALE() //returns the user's locale string, as defined by the user's browser settings. You can learn more about the locale strings here.

USERNAME() //returns the Name of the current user if known.

Common and Complex Expressions

LEN("AppSheet") //returns 8.

CONCATENATE([First Name], " ", [Last Name]) //returns a full name.

IF(([Status] = "Open"), "Green", "Red") //returns "Green" when [Status] equals "Open"; otherwise, returns "Red".

Example: Column AppName with a value of "Sales-10305"

LEFT([AppName], 5) //returns "Sales".

RIGHT([AppName], 5) //returns "10305"

LEFT([AppName], FIND("-", [AppName])) //returns "Sales"

Use the following expressions in the Initial Value feature of the app editor:

UNIQUEID() //Use to generate a unique Text value, e.g. a unique Invoice ID.

HERE() //Use to identify the user's current LatLong.

USEREMAIL() //Use to populate record value based on user login

USERNAME() //It's generally a good idea to avoid USERNAME() because it's unreliable: providers like Google only return the user's name if that user has enabled Google+ on their account. If you wish to retrieve the user's name, do the following:

Create a table having UserEmail as its key field followed by a UserName field.

Populate the table manually by adding one entry for each UserEmail address.

To obtain the user name, use a reference expression based on USEREMAIL().

Complex Expressions

LEN([_THIS]) <= 10 //Use this expression in the Valid_If constraint to restrict form field input to a maximum of 10 characters.

IF(([Status] = "Open"), "Green",

IF(([Status] = "Closed"), "Red",

IF(([Status] = "Not Started"), "Blue",

"Purple")

)

)

//Returns "Green" when Status equals Open; returns "Red" when Status equals Closed; returns "Blue" when Status equals Not Started; otherwise, returns "Purple".

**********

Math Functions

//Math functions may also be used anywhere math expressions may be used.

ABS() // Absolute value

AVERAGE() // Average of list of values

CEILING() // Nearest higher Number

FLOOR() // Nearest lower Number

LN() // Natural log

LOG() // Logarithm

LOG2() // Log base 2

LOG10() // Log base 10

MOD() // Remainder from Number division

POWER() // Exponentiation

RANDBETWEEN() // Random Number

ROUND() // Nearest Number

SQRT() // Square root

STDEVP() // Standard deviation of list of values

SUM() // Sum of list of values

**********

Yes/No Expressions

AND({condition 1},{condition 2},{condition 3},..,{condition n})

//If all the conditions in the AND expression are true, then the result of the expression will be TRUE (Yes), if any of the conditions are not true, the result will be FALSE (No). Separate each condition with a comma. Each condition is an expression in itself, so be aware of how the expressions are nested.

OR({condition 1},{condition 2},{condition 3},..,{condition n})

//If any of the conditions in the expression is TRUE then the OR expression will result in TRUE, otherwise it will be FALSE. Each condition is separated by a comma.

NOT({condition})

//If the condition result is TRUE, then wrapping it in NOT() results in FALSE. If the condition is FALSE, then wrapping it in NOT() results in TRUE.

Other Operators

//AppSheet supports the following additional operators:

ISBLANK({*}) //returns true if an expression is empty

CONTAINS({text_1},{text_2}) // returns true if text_1 contains text_2

IN({*},{List}) // returns true if a value is in a list

Common Expressions

//If you wanted to do something when the value of column Color is green and it was completed today, here's the expression you would use:

AND([Color]="Green",[CompleteDate]>TODAY())

//If you wanted to see whether there are any oranges recorded in the column Fruit, you would use this expression:

CONTAINS([Fruit],"Oranges")

Complex Expressions

//These are some examples of more complex operations where more parameters are included.

OR(

([Price]*[Quantity])>$10,000.00,

[Price]>$100.00

)

OR(

CONTAINS([Fruit],"Oranges"),

CONTAINS([Fruit],"Apples"),

CONTAINS([Fruit],"Bananas")

)

**********

Column Value Expressions

CONCATENATE(text-expression, text-expression[, ...]) //to combine two or more Text values.

HERE() //for LatLong of the current location.

IF(condition-expression, then-expression, else-expression)

IFS(condition-expression, then-expression, condition-expression, then-expression, ...) //to provide a sequence of condition-value pairs that are evaluated left to right until one of the conditions is true.

ISBLANK(expression) //to check if expression produces a blank result.

LEN(text-expression) //to get the length of a Text value.

NOW() //for the current DateTime.

ORDERBY(ref-list-expression, column[, direction[, column]]...) //to sort a list of references. The first argument must yield a list of references (i.e. a list of the keys of the records to sort), followed by one or more pairs indicating a column name to order by and its ordering direction (TRUE for descending order, FALSE for ascending order). If the data should be ordered by just one column, the ordering direction may be omitted and defaults to FALSE (for ascending order). For example:

ORDERBY([Related Orders [Customer Name]], [Order Date], FALSE)

SWITCH(expression, compare-value, result[, compare-value, result]..., default-result) //to choose one of the results based on the value of the expression. It's a simpler version of IFS().

TIMENOW() //for the current Time.

TODAY() //for the current Date.

UNIQUEID() //for a new unique Text value, e.g. for use as a key.

USEREMAIL() //for the Email of the current user.

USERNAME() //for the Name of the current user.

Common Use Cases

//In a virtual column:

CONCATENATE([FirstName], " ", [LastName])

//In a slice filter condition:

[Status] = "Complete"

//In a Valid_If column constraint:

[_THIS] > 25

**********

Dereference Expressions

//You can use a Dereference expression to retrieve the value of a column in a referenced record. Do this by writing a Dereference expression in the form

[Column Containing Reference].[Column in Referenced Table].

//You can use Dereference expressions when computing values. For example, in the Order Capture app, there is a formula that multiplies

[Product].[Price] with another column called [Quantity].

**********

Sample Expression ABS()

ABS(0) returns Number: 0

ABS(1) returns Number: 1

ABS(-1) returns Number: 1

ABS(0.0) returns Decimal: 0

ABS(3.14) returns Decimal: 3.14

ABS(-3.14) returns Decimal: 3.14

**********

Sample Expression AND()

AND(FALSE, FALSE) // FALSE

AND(FALSE, TRUE) // FALSE

AND(TRUE, FALSE) // FALSE

AND(TRUE, TRUE) // TRUE

AND(

ISNOTBLANK([Grade]),

([Grade] = "A")

)

// TRUE only if the Grade column value is both present and has a value of exactly A; otherwise FALSE

AND(

([Name] = "Acme"),

([Rating] > 3),

[Preferred?]

)

// TRUE only if the row is for the company named Acme that has a rating of 3 or higher and is a preferred vendor; FALSE otherwise.

AND(

(LEN([_THIS]) > 3),

CONTAINS([_THIS], "@")

)

// TRUE if the current column value (e.g., as with Valid_If) is at least three characters long and contains at least one @ symbol

Choose Stale Sales Prospects

//Choose rows in a table, perhaps with the FILTER() or SELECT() functions, or by a slice or security filter, that describe the current app user's sales prospects that want to be contacted but haven't been recently:

AND(

IN(

[Sales Contact],

LIST(USEREMAIL(), "")

),//2

 NOT(

  [DO NOT CALL]

 ),//4

 ([Last Contact] < (TODAY() - 30))//3

)//1

Validate Non-Overlapping Date Range

//Validate a new row's date range and ensure it does not overlap date ranges defined by existing rows in the table:

AND(

([End] > [Start]),

(COUNT(

  FILTER(

    "MyTable",

    OR(

      AND(

        ([Start] >= [_THISROW].[Start]),

        ([Start] <= [_THISROW].[End])

      ),

      AND(

        ([End] >= [_THISROW].[Start]),

        ([End] <= [_THISROW].[End])

      )

      AND(

        ([Start] < [_THISROW].[Start]),

        ([End] > [_THISROW].[End])

      )

    )

  )

) = 0)

)

AND([Color]="Green",[CompleteDate]>TODAY())

IFS(

AND(

[_THISROW_BEFORE].[status_code]<>"Completed",

[status_code]="Completed"

), NOW(),

AND(

[_THISROW_BEFORE].[status_code]="Completed",

[status_code]<>"Completed"

), "",

AND(

[_THISROW_BEFORE].[status_code]="Completed",

[status_code]="Completed" ),

[_THISROW_BEFORE].[act_end_date])

**********

Sample Expression ANY()

ANY(Students[Name]) // An arbitrary value from the Name column of the Students table. Arbitrary because the order of values in the Students[Name] column list isn't guaranteed unless wrapped in SORT(). Equivalent to:

ANY(SELECT(Students[Name], TRUE))

ANY(LIST(1, 2, 3)) //returns Number: 1

ANY({"Red", "Yellow", "Green"}) //returns Text: Red

Column Value

//A single column value from any of a set of rows

ANY(SELECT(Products[Price], ([Color] = "Orange")))

1) SELECT(Products[Price], ...) returns values in the Price column from rows in the Products table that match the selection criteria.

2) [Color] = "Orange" limits the selection to only those rows with a Color column value of exactly Orange.

3) ANY(...) returns one arbitrary value from the list of column values.

//Equivalent to:

LOOKUP("Orange", "Products", "Color", "Price")

Highest Value in Column

//The highest product price

ANY(TOP(SORT(Products[Price], TRUE), 1))

1) Products[Price] retrieves the list of all values from the Price column of the Products table.

2) SORT(..., TRUE) orders the list of prices numerically in descending/high-to-low order (TRUE).

3) TOP(..., 1) removes all but the first price in the sorted list.

4) ANY(...) returns the one remaining price from the top list.

//Equivalent to

MAX(Products[Price])

Preferred Value

//A mobile, office, or home phone number chosen from those that aren't blank

ANY(

TOP(

(

LIST([Mobile Phone], [Office Phone], [Home Phone])

- LIST("")

),

1

)

)

1) LIST([Mobile Phone], [Office Phone], [Home Phone]) constructs a list of the three numbers.

2) LIST(...) - LIST("") removes any blank items from the list of numbers.

3) TOP(..., 1) removes all but the first from the list of non-blank numbers.

4) ANY(...) returns the only remaining non-blank number from the top list.

//Equivalent to:

INDEX(

(

LIST([Mobile Phone], [Office Phone], [Home Phone])

- LIST("")

),

1

)

Row with Highest Value in Column

//The row of the student with the highest GPA in Mr Sandwich's class:

ANY(//7

TOP(//6

ORDERBY(//5

FILTER(

"Students", //1

AND(//2

ISNOTBLANK([Teacher]),//3

([Teacher] = "Mr Sandwich")//4

)

),

[GPA], TRUE

),

1

)

)

1) FILTER("Students", ...) returns a list of key values from the Students table that match a condition.

2) AND(..., ...) limits the filter to only those rows that match all of the given sub-conditions.

3) ISNOTBLANK([Teacher]) requires the Teacher column value not be blank.

4) [Teacher] = "Mr Sandwich" requires the Teacher column value be exactly Mr Sandwich.

5) ORDERBY(..., [GPA], TRUE) orders the filtered keys by the values of their corresponding GPA column value in descending/high-to-low order (TRUE), putting high GPAs first.

6) TOP(..., 1) removes all but the first item in the ordered list, leaving only the key of the row having the highest GPA.

7) ANY(...) returns the one remaining entry from the top list: the key of the row corresponding to the student with the highest GPA in Mr Sandwich's class.

Equivalent to:

MAXROW(

"Students", "GPA",

AND(

ISNOTBLANK([Teacher]),

([Teacher] = "Mr Sandwich")

)

)

Common Problems

ANY(1, 2, 3) // the arguments are not in list form. To fix, wrap them in LIST() to construct a list

ANY(LIST(1, 2, 3))

**********

Sample Expression AVERAGE()

AVERAGE(Products[Price]) // The average of the all values in the Price column of the Products table.

Equivalent to:

AVERAGE(SELECT(Products[Price], TRUE))

AVERAGE([Discounts]) // The average of the all items in the Discounts column value, where Discounts is of type List for some numeric type.

AVERAGE(LIST(1, 2, 3)) // 2.00

AVERAGE(LIST(0) - LIST(0)) //(a valid constructed list with a type but no items): 0.00

Average of Select Rows

//Compute average product rating from customer feedback within the past month, excluding feedback with no rating:

AVERAGE(

SELECT(

Feedback[Product Rating],

AND(

([Product Rating] > 0),

([Submitted] >= (EOMONTH(TODAY(), -2) + DAY(TODAY()))) // one month prior today?

)

)

)

1) SELECT(Feedback[Product Rating], ...) gets a list of Product Rating values from select rows of the Feedback table.

2) AND(..., ...) limits the SELECT() results to only those rows that match all of the conditions.

3) ([Product Rating] > 0) limits the selection to only rows with a Product Rating value greater than zero, where 0 is the default value and indicates "no rating".

4) ([Submitted] >= ...) further limits the rows to those with a submission date on or after the computed date.

5) (EOMONTH(TODAY(), -2) + DAY(TODAY())) computes the date one month prior to today.

6) AVERAGE(...) computes the average of the selected values.

Common Problems

AVERAGE(1, 2, 3) // the arguments are not in list form. To fix, wrap them in LIST() to construct a list:

AVERAGE(LIST(1, 2, 3))

***********

Sample Expression CEILING()

CEILING(3.14) : 4

CEILING(-3.14) : -3

CEILING(5) : 5

CEILING(100*10/7.2)/100.0

CEILING([YourDecimalValue]*4)*0.25

***********

Sample Expression CONCATENATE()

CONCATENATE("Good", "morning", "!") : Goodmorning!

CONCATENATE("Good", " ", "morning", "!") : Good morning!

CONCATENATE("Good morning, ", [First Name], "!") : Good morning, Martin!

CONCATENATE([Last Name], ", ", [First Name]) : Sandwich, Martin

CONCATENATE("Today is ", MONTH("4/1/2010"), "/", DAY("4/1/2010"), ".") : Today is 4/1.

Include the Weekday Name

//Include the computed weekday name into a constructed statement:

CONCATENATE(

"Today is ",

INDEX(

   {"Sunday", "Monday", "Tuesday", "Wednesday",

     "Thursday", "Friday", "Saturday", "Sunday"},

   WEEKDAY("4/1/2010")

 ),

 "."

)

1) WEEKDAY(...) returns the weekday number for the given Date or DateTime value: 5

2) INDEX({...}, ...) uses the weekday number to retrieve the weekday name from a list of weekday names: Thursday

3) CONCATENATE("Today is ", ..., ".") assembles all of the parts into the final text: Today is Thursday.

**********

Sample Expression CONTAINS()

CONTAINS("abc", "a") // TRUE

CONTAINS("abc", "bc") // TRUE

CONTAINS("abc", "d") // FALSE

CONTAINS("I'm bored!", "Red")  // TRUE

CONTAINS([Address], "PO Box") //answers the question: does the Address indicate a post office box?

CONTAINS([Email], "@") //answers the question: does the Email contain at least one at sign (@)?

CONTAINS(".?!", LEFT([Sentence], 1)) //answers the question: is the left-most character of the value of the Sentence (LEFT([Sentence], 1)) a period (.), question mark (?), or exclamation mark (!) (".?!")? Equivalent to:

IN(LEFT([Sentence], 1), {".", "?", "!"})

//If you wanted to see whether there are any oranges recorded in the column Fruit, you would use this expression:

CONTAINS([Fruit],"Oranges")

***********

Sample Expressions CONTEXT()

IN(CONTEXT("View"), LIST("Orders_Detail", "Orders_Form")) //gives a Yes/No value that answers the question, is the currently-displayed view named Orders_Detail or Orders_Form? This might be used in a Show_If column constraint to display a column only in specific views but hide it elsewhere

("Form" = CONTEXT("ViewType")) //gives a Yes/No value that answers the question, is the currently-displayed view a form view? This might be used in a Show_If column constraint to display a column only in form views but hide it elsewhere.

SWITCH(CONTEXT("Host"), "Device", "Tap below to:", "Browser", "Click below to:", "") //gives different directions depending on whether the app is running from the native app on a device or from a browser. This might be used to generate a display name for a gallery view of launchers

***********

Sample Expression COUNT()

COUNT(Products[Color]) //The total number of (possibly duplicate) values in the Color column of the Products table.

//Equivalent to

COUNT(SELECT(Products[Color], TRUE, FALSE))

COUNT(SELECT(Products[Color], TRUE, TRUE)) // The total number of non-duplicate values in the Color column of the Products table.

COUNT(SELECT(Products[Color], IN([Color], {"Red", "Orange"}))) // The total number of (possibly duplicate) values in the Color column of the Products table where the Color column value is either Red or Orange.

COUNT(Orders[_RowNumber]) // The total number of rows in the Orders table. Note that this is not equivalent to MAX(Orders[_RowNumber]), which doesn't account for empty (e.g., deleted) rows or a spreadsheet header row.

COUNT([Discounts]) // The count of the items in the Discounts column value, where Discounts is of type List

COUNT(LIST("Red", "Yellow", "Green")) // 3

COUNT(LIST()) // 0

Count Select Rows

//Count orders with special delivery instructions that occur within a reporting period:

COUNT(

 FILTER(

   "Orders",

   AND(

     ISNOTBLANK([Special Instructions]),

     ([Date Done] >= [_THISROW].[Begin Date]),

     ([Date Done] < [_THISROW].[End Date])

   )

 )

)

1) FILTER("Orders",  ...) gets a list of row references (values of the key column)  for select rows in Orders.

2) AND(..., ..., ...) limits the results to only those rows that match all of the conditions.

2.1) ISNOTBLANK([Special Instructions]) ensures only rows that contain special delivery instructions are counted.

2.2) ([Date Done] >= [_THISROW].[Begin Date]) limits the count to only rows with a Date Done column value no earlier than the report's Begin Date column value.

2.3) ([Date Done] < [_THISROW].[End Date]) further limits the rows to those with dates before the report's end date.

3) COUNT(...) counts the number of items selected.

Common Problems

COUNT(1, 2, 3) // The arguments are not in list form. To fix, wrap them in LIST() to construct a list:

COUNT(LIST(1, 2, 3))

***********

Sample Expression DATE()

DATE("4/1/2010") // 4/1/2010

DATE(TODAY()) // Today's date from a Date value.

DATE("4/1/2010 3:14") // 4/1/2010

DATE(NOW()) // Today's date from a DateTime value.

DATE("3:14") // 12/30/1899 (a Time value has no date component, so a default is used).

DATE(TIMENOW()) // 12/30/1899 (TIMENOW() returns a Time value, which has no date component, so a default is used).

Common Problems

DATE("Good morning, Martin!") // Returns blank because the textual input isn't a recognized temporal type and so cannot be converted.

***********

Sample Expression DATETIME()

DATETIME("4/1/2010 3:14") //(24-hour time if no AM/PM): 4/1/2010 3:14:00 AM

DATETIME("4/1/2010 15:14") //(24-hour time): 4/1/2010 3:14:00 PM

DATETIME("4/1/2010 3:14 PM") //(12-hour time with AM/PM): 4/1/2010 3:14:00 PM

DATETIME(NOW()) // The current date & time from a DateTime value.

DATETIME("4/1/2010") // 4/1/2010 12:00:00 AM (a Date value has no time component, so a default is used).

DATETIME(TODAY()) // Today's date at 12:00:00 AM from a Date value (TODAY() returns a Date value, which has no time component, so a default is used).

DATETIME("3:14") // 12/30/1899 3:14:00 AM (a Time value has no date component, so a default is used).

DATETIME(TIMENOW()) // 12/30/1899 at the current time (TIMENOW() returns a Time value, which has no date component, so a default is used).

Common Problems

DATETIME("Good morning, Martin!") // Returns blank because the textual input isn't a recognized temporal type and so cannot be converted.

***********

Sample Expression DAY()

DAY(TODAY()) // Today's day of the month

DAY(TODAY()) + 1 // Tomorrow's day of the month.

DAY(TODAY()) - 1 // Yesterday's day of the month.

DAY([Birthday]) // Someone's birthday day of the month.

Ordinal Day Number

//Convert today's day number to a numeric ordinal (1st, 2nd, 3rd, 4th, etc.):

CONCATENATE(

DAY(TODAY()),

IF(

IN(

DAY(TODAY()),

LIST(11, 12, 13)

),

   "th",

   SWITCH(

     RIGHT(DAY(TODAY()), 1),

     "1", "st",

     "2", "nd",

     "3", "rd",

     "th"

   )

 )

)

1) IF(..., ..., ...) determines whether today's day of the month is handled as an ordinal naming exception, or as normal.

2) IN(..., ...) tests whether today's day of the month is one of the exceptions.

3) DAY(TODAY()) returns today's day of the month (1-31).

4) LIST(11, 12, 13) defines the list of numbers that are exception to the ordinal naming rules (e.g., 11 is 11th, not 11st; 12 is 12th, not 12nd).

5) "th" is the suffix given to exception days.

6) SWITCH(...) determines which suffix to give non-exceptions.

7) RIGHT(DAY(TODAY()), 1) gets the rightmost digit of the day of the month.

8) "1", "st", "2", "nd", and "3", "rd" gives days that end with 1 the suffix -st (e.g., 31st), 2 the suffix -nd (e.g., 2nd), and 3 -rd (e.g., 23rd).

9) "th" is he suffix given all other (non-exception) days (e.g., 5th, 15th, 20th).

10) CONCATENATE(DAY(TODAY()), ...) appends the computed suffix to today's day of the month number.

***********

Sample Expression EOMONTH()

EOMONTH("3/15/2020", 3) // 6/30/2020

EOMONTH("2/1/2020", 0) // 2/29/2020

EOMONTH(TODAY(), 0) or EOMONTH(NOW(), 0) // Last day of the current month.

//Equivalent to

(DATE((MONTH(TODAY()) + 1) & "/1/" & YEAR(TODAY())) - 1)

EOMONTH(TODAY(), 1) // Last day of next month

EOMONTH(TODAY(), -1) // Last day of last month.

//Equivalent to

(TODAY() - DAY(TODAY()))

EOMONTH(TODAY(), 0) + 1 // First day of next month.

//Equivalent to

DATE((MONTH(TODAY()) + 1) & "/1/" & YEAR(TODAY())).

EOMONTH(TODAY(), -1) + 1 : First day of this month

//Equivalent to

(TODAY() - DAY(TODAY()) + 1)

EOMONTH(TODAY(), -2) + 1 // First day of last month.

EOMONTH(TODAY(), 0) + DAY(TODAY()) // One month from today.

EOMONTH(TODAY(), -1) + DAY(TODAY()) // Today.

//Equivalent to

TODAY().

EOMONTH(TODAY(), -2) + DAY(TODAY()) // One month ago today.

(DAY(EOMONTH(("2/1/" & (YEAR(TODAY()) + 1)), 0)) > 28) //(returnsYes/No): TRUE if next year is leap year.

***********

Sample Expression EOWEEK()

EOWEEK(TODAY()) // The date of Saturday of this week from a Date

(EOWEEK(TODAY()) + 2) // The date of Monday of next week

(EOWEEK(TODAY() + 7)) or (EOWEEK(TODAY()) + 7) // The date of Saturday of next week

(EOWEEK(TODAY()) - 7) // The date of Saturday of last week.

//Equivalent to

(TODAY() - WEEKDAY(TODAY()))

EOWEEK(NOW()) // The date of Saturday of this week from a DateTime

EOWEEK("3/15/2020 14:00:00") // 3/21/2020 (from a DateTime)

EOWEEK("2/1/2020") // 2/1/2020 (from a Date)

***********

Sample Expression ENCODEURL()

ENCODEURL("Hello") //Hello

ENCODEURL("Hello!") //Hello%21

ENCODEURL("Hello there!") // Hello%20there%21

("https://www.google.com/search?q=" & ENCODEURL("AppSheet is fun!"))

// https://www.google.com/search?q=AppSheet%20is%20fun%21

***********

Sample Expression FIND()

FIND("a", "abc") // 1

FIND("bc", "abc") // 2

FIND("d", "abc") // 0

FIND("Red", "I'm bored!") // returns 0 because Red--with an uppercase R--doesn't occur in I'm bored!.

FIND(UPPER("Red"), UPPER("I'm bored!")) //returns 7 because the all-uppercase RED occurs at that position in the all-uppercase I'M BORED!.

FIND("@", [Email]) //returns the position of the first at sign (@) in Email, or 0 if not present.

***************

Sample Expression FILTER()

FILTER("Orders",

([Customer] = [_THISROW].[Customer])

)

//returns keys to rows in the Orders data set in which the Customers column value is equal to the Customers column value of the current form (i.e., orders for this customer).

FILTER("Products",

([Price] < 100)

)

//returns keys to rows of the Products data set in which the Price column value is less than 100 (i.e., products priced less than $100).

FILTER("Students",

TRUE

)

//returns keys to all rows of the Students data set.

//Equivalent to

SELECT(Students[Student ID], TRUE) //if Student ID is the key column for Students

FILTER("MyTable", FALSE) : returns an empty list because the select-row? expression will always return FALSE, excluding all rows.

Open Issues Assigned to Me

FILTER(

 "Issues",

 AND(

   ([Status] = "Open"),

   ([Assigned to] = USEREMAIL())

 )

)

/*

1) FILTER("Issues", ...) gets list of keys from select rows in the Issues data set.

2) AND(..., ...) limits the rows selected to only those that match both of the criteria.

3) ([Status] = "Open") limits the selection to only those rows in which the Status column value is Open (i.e., only open issues).

4) ([Assigned to] = USEREMAIL()) further limits the selection to only rows with an Assigned to column value equal to the current app user's email address (i.e., issues assigned to me).*/

Orders with Special Instructions

FILTER(

 "Orders",

 AND(

   ISNOTBLANK([Special Instructions]),

   ([Date Done] >= [_THISROW].[Begin Date]),

   ([Date Done] < [_THISROW].[End Date])

 )

)

/*

1) FILTER("Orders",  ...) gets a list of row references (values of the key column)  for select rows in Orders.

2) AND(..., ..., ...) limits the results to only those rows that match all of the conditions.

3) ISNOTBLANK([Special Instructions]) limits the selection to only rows that contain special delivery instructions.

4) ([Date Done] >= [_THISROW].[Begin Date]) limits the count to only rows with a Date Done column value no earlier than the report's Begin Date column value.

5) ([Date Done] < [_THISROW].[End Date]) further limits the rows to those with dates before the report's end date.*/

Common Problems

FILTER(Text, ([Ticket ID] = [_THISROW].[Ticket ID]))

//produces the error, Expression [...] could not be parsed due to exception: #VALUE!. In this example, the data set name, Text, has significance within the internals of AppSheet and causes confusion. Any data set name that matches an AppSheet or Excel function name may produce this problem. To fix, quote the problem name:

FILTER("Text", ([Ticket ID] = [_THISROW].[Ticket ID]))

FILTER("Events", ([Venue] <> [Wanted Venue]))

//produces the error, Unable to find column [...]. Column references within the select-row? expression (e.g., [Venue]) are to the row being considered as the data set is searched. To access columns outside the row being considered, such as when using FILTER() from within a column constraint, app formula, initial value, or format rule, reference the external column using _THISROW:

FILTER("Events", ([Venue] <> [_THISROW].[Wanted Venue]))

***************

Sample Expression HYPERLINK()

HYPERLINK("https://www.google.com", "Google") :  Google

HYPERLINK("https://www.appsheet.com", "AppSheet home page") :  AppSheet home page

HYPERLINK([Vendor Site], [Vendor Name]) : A hyperlink pointing to the URL in the Vendor Site column, with clickable text from the Vendor Name column.

Encode Text for URL

Certain characters, such as ampersand (&), colon (:), slash (/), and even a space, have special meanings within URLs. Text included in URLs that contains special characters should be "encoded" to remove the special meaning and avoid problems. The ENCODEURL() function is provided for this purpose.

HYPERLINK(

 CONCATENATE(

   "https://www.google.com/search?q=",

   ENCODEURL([Product Name])

 ),

 ("Search for " & [Product Name] & " on Google")

)

1) HYPERLINK(..., ...) creates a hyperlink to the given target URL and with the given clickable text.

2) CONCATENATE(..., ...) computes the target URL by combining the given values.

3) "https://www.google.com/search?q=": the initial part of the target URL. This part is already properly encoded as a URL, so ENCODEURL() is not needed.

4) ENCODEURL([Product Name]) encodes the Product Name column value, ensuring the special meaning is removed from any special characters the value may contain.

5) ("Search for " & [Product Name] & " on Google") assembles the clickable text for the hyperlink from the given components. ENCODEURL() is not needed here because the clickable text is not part of the URL.

Equivalent to CONCATENATE("Search for ", [Product Name], " on Google").

***************

Sample Expression IN()

IN("a", {"a", "b", "c"}) // TRUE

IN("bc", {"a", "b", "c"}) // FALSE

IN("d", {"a", "b", "c"}) // FALSE

IN("Red", {"I'm bored!"}) // FALSE

IN("@", LIST([Email])) //answers the question: is the value of the Email column exactly @ and nothing else?

//Equivalent to

([Email] = "@")

IN(LEFT([Sentence], 1), {".", "?", "!"})

//answers the question: is the left-most character of the value of the Sentence (LEFT([Sentence], 1)) a period (.), question mark (?), or exclamation mark (!)?

//Equivalent to

CONTAINS(".?!", LEFT([Sentence], 1))

IN(USEREMAIL(), AppUsers[Email])

//answers the question: is the current app user's email address (USEREMAIl()) found in the list of Email addresses in the AppUsers table (AppUsers[Email])?

IN(CONTEXT("ViewType"), {"Deck", "Gallery", "Table"})

//answers the question: is the currently-displayed view's type Deck, Gallery, or Table?

//Equivalent to

OR(

(CONTEXT("ViewType") = "Deck"),

(CONTEXT("ViewType") = "Gallery"),

(CONTEXT("ViewType") = "Table")

)

*************

Sample Expression INDEX()

INDEX(Students[Name], 1) // An arbitrary value from the Name column of the Students table. Arbitrary because the order of values in the Students[Name] column list isn't guaranteed unless wrapped in SORT(). Equivalent to

ANY(Students[Name])

INDEX(LIST("Red", "Yellow", "Green"), 2) //returns Text: Yellow

INDEX({"Red", "Yellow", "Green"}, 4) //returns blank (4 is outside the list).

Highest Value in Column

//The highest product price:

INDEX(SORT(Products[Price], TRUE), 1)

//1) Products[Price] retrieves the list of all values from the Price column of the Products table.

//2) SORT(..., TRUE) orders the list of prices numerically in descending/high-to-low order (TRUE).

//3) INDEX(..., 1) returns the first price in the sorted list.

//Equivalent to

MAX(Products[Price])

Month Number to Name

//Today's month name (returns Text):

INDEX(

 {

   "January", "February", "March",

   "April", "May", "June",

   "July", "August", "September",

   "October", "November", "December"

 },

 MONTH(TODAY())

)

//1) {"January", ...} constructs a list of month names.

//2) TODAY() returns today's date.

//3) MONTH(...) converts a Date value to a number corresponding to the month of the year.

//4) INDEX(..., MONTH(...)) uses the month number to choose a month name from the list.

Equivalent to:

SWITCH(

 MONTH(TODAY()),

 1, "January",

 2, "February",

 3, "March",

 4, "April",

 5, "May",

 6, "June",

 7, "July",

 8, "August",

 9, "September",

 10, "October",

 11, "November",

 12, "December",

 ""

)

Preferred Value

//A mobile, office, or home phone number chosen from those that aren't blank:

INDEX(

 (

   LIST([Mobile Phone], [Office Phone], [Home Phone])

   - LIST("")

 ),

 1

)

//1) LIST([Mobile Phone], ...) constructs a list of the three numbers.

//2) LIST(...) - LIST("") removes any blank items from the list of numbers.

//3) INDEX(..., 1) returns the first of the remaining items of the list.

Equivalent to:

ANY(

 TOP(

   (

     LIST([Mobile Phone], [Office Phone], [Home Phone])

     - LIST("")

   ),

   1

 )

)

Row with Highest Value in Column

//The row of the student with the highest GPA in Mr Sandwich's class (returns Ref):

INDEX(

 ORDERBY(

   FILTER("Students",

     AND(

       ISNOTBLANK([Teacher]),

       ([Teacher] = "Mr Sandwich")

     )

   ),

   [GPA], TRUE

 ),

 1

)

/*

1) FILTER("Students", ...) returns a list of key values from the Students table that match a condition.

2) AND(..., ...) limits the filter to only those rows that match all of the given sub-conditions.

3) ISNOTBLANK([Teacher]) requires the Teacher column value not be blank.

4) [Teacher] = "Mr Sandwich" requires the Teacher column value be exactly Mr Sandwich.

5) ORDERBY(..., [GPA], TRUE) orders the filtered keys by the values of their corresponding GPA column value in descending/high-to-low order (TRUE), putting high GPAs first.

6) INDEX(..., 1) returns the first item in the ordered list, the key of the row having the highest GPA.

*/

Equivalent to:

MAXROW(

 "Students", "GPA",

 AND(

   ISNOTBLANK([Teacher]),

   ([Teacher] = "Mr Sandwich")

 )

)

Weekday Number to Name

Today's weekday name (returns Text):

INDEX(

 {

   "Sunday", "Monday", "Tuesday",

   "Wednesday", "Thursday", "Friday",

   "Saturday"

 },

 WEEKDAY(TODAY())

)

/*

1) {"Sunday", ...} constructs a list of weekday names.

2) TODAY() returns today's date.

3) WEEKDAY(...) converts a Date value to a number corresponding to the day of the week.

4) INDEX(..., WEEKDAY(...)) uses the weekday number to choose a weekday name.

*/

Equivalent to:

SWITCH(

 WEEKDAY(TODAY()),

 1, "Sunday",

 2, "Monday",

 3, "Tuesday",

 4, "Wednesday",

 5, "Thursday",

 6, "Friday",

 7, "Saturday",

 ""

)

*************

Sample Expression INTERSECT()

INTERSECT(LIST("Red", "Blue", "Green"), LIST("Orange", "Blue")) //returns a list with one item: Blue.

INTERSECT(Employees[Name], LIST("Bob")) //returns a list of one item, Bob, if that name occurs in the list of employee names.

Equivalent to

IFS(IN("Bob", Employees[Name], LIST("Bob"))

INTERSECT(Employees[Name], Employees[Name]) //returns a list of unique names in the Employees[Name] column.

Equivalent to

UNIQUE(Employees[Name])

*************

Sample Expression ISBLANK()

ISBLANK("") // TRUE

NOT(ISBLANK("")) // FALSE.

Equivalent to

ISNOTBLANK("")

ISBLANK("Hi!") // FALSE

ISBLANK(0) // FALSE

ISBLANK(LIST()) // TRUE

ISBLANK(LIST("Red", "Yellow", "Green")) // FALSE

ISBLANK([Address]) // TRUE if the Address column has no value, FALSE otherwise.

Equivalent to

("" = [Address]) //(but it is not equivalent to

([Address] = "") //see Common Problems, below).

ISBLANK(FILTER("Customers", ([City] = "London"))) // TRUE if no customers are in London. Equivalent to

(COUNT(FILTER("Customers", ([City] = "London"))) = 0)

Common Problems

//ISBLANK([Address]) is not equivalent to ([Address] = "") because the is-equal-to operator (=) will always return TRUE if the second operand is a blank value, regardless of the first operator. Swapping the operands (e.g., ("" = [Address])) works around this behavior.

*************

Sample Expression ISNOTBLANK()

ISNOTBLANK("") // FALSE.

Equivalent to

NOT(ISBLANK(""))

NOT(ISNOTBLANK("")) // TRUE.

Equivalent to

ISBLANK("").

ISNOTBLANK("Hi!") // TRUE

ISNOTBLANK(0) // TRUE

ISNOTBLANK(LIST()) // FALSE

ISNOTBLANK(LIST("Red", "Yellow", "Green")) // TRUE

ISNOTBLANK([Address]) // TRUE if the Address column has a value, FALSE otherwise.

ISNOTBLANK(FILTER("Customers", ([City] = "London"))) // TRUE if any customers are in London.

Equivalent to

(COUNT(FILTER("Customers", ([City] = "London"))) <> 0)

IFS(

COUNT(

SELECT(Tablename[Date],

[Date]=TODAY()))>0,

IFS(

AND(

ISBLANK(

LOOKUP(TODAY(),"Tablename","Date","ColumnA")

),

ISBLANK(LOOKUP(TODAY(),"Tablename","Date","ColumnB")

),

ISNOTBLANK(LOOKUP(TODAY(),"Tablename","Date","ColumnC")

),

ISNOTBLANK(LOOKUP(TODAY(),"Tablename","Date","ColumnD"))

),

LINKTOROW(TODAY(),"SliceAB_Form"),

AND(

ISNOTBLANK(LOOKUP(TODAY(),"Tablename","Date","ColumnA")

),

ISNOTBLANK(LOOKUP(TODAY(),"Tablename","Date","ColumnB")

),

ISBLANK(LOOKUP(TODAY(),"Tablename","Date","ColumnC")

),

ISBLANK(LOOKUP(TODAY(),"Tablename","Date","ColumnD")) ),

LINKTOROW(TODAY(),"SliceCD_Form")

)

)

COUNT(

SELECT(

Orders[_ROWNUMBER],

AND(

ISNOTBLANK([SpecialInstructions]),

([DateDone] >= [_THISROW].[BeginDate]),

([DateDone] < [_THISROW].[EndDate]))

)

)

*************

Sample Expression LEFT()

LEFT("123 Maple Dr", 5) //123 M

LEFT("123 Maple Dr", 0) //returns blank

First & Last Initials

//Initials from a first and last name:

LEFT([First Name], 1) & LEFT([Last Name], 1)

/*

1) LEFT(..., 1) gets the first character of the specified column value.

2) LEFT(...) & LEFT(...) concatenates the two initials into a single text value.*/

Equivalent to

INITIALS(CONCATENATE([First Name], " ", [Last Name])).

Integer Component of Decimal Value

//The integer component of a Decimal value in the Result column:

NUMBER(

 LEFT(

   TEXT([Result]),

   (FIND(".", TEXT([Result])) - 1)

 )

)

/*

1) TEXT([Result]) converts the Decimal value to a Text value. The textual functions used in this example interpret non-textual values differently. Using TEXT() ensures the Result column value is interpreted the same by each function.

2) (FIND(".", ...) - 1) locates the character immediately preceding the decimal point.

3) LEFT(..., ...) extracts the integer part.

4) NUMBER(...) converts the extracted text to a Number.

*/

Equivalent to

FLOOR([Result])

Street Number from Street Address

//The first word of an address, which is typically the street number:

LEFT([Address], (FIND(" ", [Address]) - 1))

/*

1) FIND(" ", [Address]) locates the first/leftmost space in the Address column value.

2) FIND(...) - 1 locates the character immediately preceding the first space.

3) LEFT([Address], ...) returns the leftmost text preceding the first space.

*/

Equivalent to:

INDEX(SPLIT([Address], " "), 1).

*************

Sample Expression LEN()

LEN("Hello") // 5

LEN([Home Phone]) // The length of the Home Phone column value.

LEN("") // 0

(LEN([Some Column] & "") = 0) // TRUE if Some Column has no value.

Equivalent to

ISBLANK([Some Column])

(LEN([Some Column] & "") > 0) // TRUE if Some Column has a value.

Equivalent to

ISNOTBLANK([Some Column])

(LEN([Serial Number]) = 10) // TRUE if Serial Number is exactly 10 characters long. Suitable for use in a Valid_If column constraint or a format rule.

*************

Sample Expression LIST()

LIST(7, 4, 95) //produces a List containing the three Number values.

//Equivalent to

{ 7, 4, 95}

LIST([Home], [Work], [Mobile]) //produces a List of the Phone values in the given columns of the current row.

LIST() //produces an empty list, useful in IF(), IFS(), or SWITCH() to return a blank value

//You can remove entries from a list by subtracting another list from it:

LIST("Bob", "Harry", "Tom") - LIST("Harry")

//This would give you a list with only Bob and Tom.

***************

Sample Expression LINKTOFILTEREDVIEW()

LINKTOFILTEREDVIEW(

 "Table A",

 (

   COUNT([meso] - LIST())

   <> COUNT([meso] - [_THISROW].[meso])

 )

)

/*

1) COUNT([meso] - LIST()) counts the distinct items of [meso]. Subtracting one list from another has the side-effect of removing duplicate list items.

2) COUNT([meso] - [_THISROW].[meso]) counts the distinct items of [meso] that are not also present in [_THISROW].[meso].

3) (COUNT(...) <> COUNT(...)) asks whether the count of (distinct) items in [meso] changes if items also in [_THISROW].[meso] are removed. If [_THISROW].[meso] contains items also in [meso], the counts will be different.*/

LINKTOFILTEREDVIEW("Some Table View", [Date Column] = TODAY())

LINKTOFILTEREDVIEW(“Order View”, [Order Status] = Open)

//will navigate to the “Order View” and display Orders whose Order Status is “Open”. The second argument to LINKTOFILTEREDVIEW is a filter expression over the rows in the view. This is similar to the filter in SELECT expressions.

LINKTOFILTEREDVIEW(“Customers Table View”, AND([Sales Rep] = [_THISROW].[Sales Rep Id], [Priority] = High))

//will navigate to the “Customers Table View” and display high-priority Customers associated with the current sales rep.

LINKTOFILTEREDVIEW(“Students Table View”, [Age] < [_THISROW].[Age])

//will navigate to the “Students Table View” and display Students having ages younger than the current student.

LINKTOFILTEREDVIEW("Inventory list_Detail", [Description] = [_THISROW].[Scanner])

LINKTOFILTEREDVIEW(“AllProperties_Results”, IN([Neighborhood_name], [_THISROW].[Neighborhood_name]))

LINKTOFILTEREDVIEW("Inventory", IN([Product Code], LIST(“100001”,“100002”, "100003")))

CONCATENATE(

LINKTOFILTEREDVIEW(

"Inventory",

IN([Product Code], LIST(“100001”,“100002”))

),

"&quickedit=true"

)

LINKTOFILTEREDVIEW(“Student Names”, [Program Name]=[_THISROW].[Program Name])

***************

Sample Expression LOOKUP()

LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone") // Gets the value of the Phone column from a row in the Restaurants data set with a Name column value that matches Bob's Burgers.

//Equivalent to

ANY(SELECT(Restaurants[Phone], ([Name] = "Bob's Burgers")))

LOOKUP([_THISROW], "Managers", "Location", "Name") // From a location row, gets the name of a manager whose assigned location is the one identified by the current row.

//Equivalent to

ANY(SELECT(Managers[Name], ([Location] = [_THISROW])))

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")) // From an order detail row, gets the order date from the parent order.

//Equivalent to

ANY(SELECT(Orders[Order Date], ([Order ID] = [_THISROW].[Order ID])))

//or

[Order ID].[Order Date].

Troubleshooting

//LOOKUP() is effectively a wrapper to the SELECT() function. As with SELECT(), any column references are interpreted from the perspective of the data set being searched, not that of the data set from which the expression is run.

For example, consider this attempt from an order detail row to get the parent order's order date:

LOOKUP([Order ID], "Orders", "Order ID", "Order Date")

//While this would produce a result, the result would likely be incorrect. Consider the equivalent SELECT() expression:

ANY(SELECT(Orders[Order Date], ([Order ID] = [Order ID])))

//The comparison matches the order's order ID against itself, which will always be true. Therefore, the SELECT() matches all orders, not just the parent of the order detail row as intended.

//To reference the column values of the row from which LOOKUP() is used, dereference _THISROW. For example:

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

The equivalent SELECT() expression:

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

We use two lookup formulas in this example:

LOOKUP([Chosen Email], "Lookup Table", "Email", "Name")

LOOKUP([Chosen Email], "Lookup Table", "Email", "Color")

//These functions find the row of "Lookup Table" whose "Email" column equals the email address entered by the user. Then, it returns the "Name" or "Color" value for that row.

//The list of suggested values is optionally defined by an expression in the column definition. The expression could be a constant

LIST("Tech", "Media")

//or a dynamic expression

SELECT(LookupTable[Option], [Category] = [_THISROW].[Category])

***************

Sample Expression MIN()

MIN(Products[Price]) // The lowest of all values in the Price column of the Products table. Equivalent to

MIN(SELECT(Products[Price], TRUE))

MIN([Discounts]) // The lowest of the items in the Discounts column value, where Discounts is of type List.

MIN(LIST(1, 2, 3)) //returns Number: 1

Lowest from Select Values

//Lowest sale price to non-employees within the past six months:

MIN(

 SELECT(

   Sales[Sale Price],

   AND(

     ISNOTBLANK([Sale Price]),

     NOT([Customer].[Is Employee?]),

     ([Sale Date] > (

       EOMONTH(TODAY(), -7) + DAY(TODAY())

     ))

   )

 )

)

/*

1) SELECT(Sales[Sale Price], ...) returns a list of values from the Sale Price column from rows of the Sales table.

2) AND(..., ..., ...) limits the values returned to only those from rows that match all of the given conditions.

3) ISNOTBLANK([Sale Price]) includes only rows with a non-blank Sale Price column value.

4) NOT([Customer].[Is Employee?]) excludes rows with a Customer column value that refers to a customer record that indicates the customer is also an employee.

5) [Sale Date] > ... includes only rows with a Sale Date column value later than the computed date.

6) EOMONTH(TODAY(), -7) + DAY(TODAY() computes the date for six month ago today.

7) MIN(...) returns the lowest value in the select list of Sale Price values.

*/

Common Problems

MIN(1, 2, 3) // the arguments are not in list form. To fix, wrap them in LIST() to construct a list:

MIN(LIST(1, 2, 3))

***************

Sample Expression MAX()

MAX(Products[Price]) // The highest of all values in the Price column of the Products table. Equivalent to

MAX(SELECT(Products[Price], TRUE))

MAX([Discounts]) // The highest of the items in the Discounts column value, where Discounts is of type List.

MAX(LIST(1, 2, 3)) //returns Number: 3

Highest from Select Values

//Highest discount given to non-employees within the past month:

MAX(

 SELECT(

   Sales[Discount Amt],

   AND(

      NOT([Customer].[Is Employee?]),

      ([Sale Date]

      >

      (EOMONTH(TODAY(), -2) + DAY(TODAY()))

    )

   )

 )

)

/*

1) SELECT(Sales[Discount Amt], ...) returns a list of values from the Discount Amt column from rows of the Sales table.

2) AND(..., ...) limits the values returned to only those from rows that match all of the given conditions.

3) NOT([Customer].[Is Employee?]) excludes those rows with a Customer column value that refers to a customer record that indicates the customer is also an employee.

4) [Sale Date] > ... includes only those rows with a Sale Date column value later than the computed date.

5) EOMONTH(TODAY(), -2) + DAY(TODAY()) computes the date for one month ago today.

6) MAX(...) returns the highest value in the select list of Discount Amt values.

*/

Common Problems

MAX(1, 2, 3) // the arguments are not in list form. To fix, wrap them in LIST() to construct a list:

MAX(LIST(1, 2, 3))

**************

Sample Expression MINROW()

MINROW("Products", "Discount") // returns the key value for the row in the Products data set that has the lowest value in the Discount column (i.e., which product has the lowest discount?). Equivalent to

MINROW("Products", "Discount", TRUE)

MINROW("MyTable", "_ROWNUMBER") // returns the key value for the row in the MyTable data set that has the lowest value in the _ROWNUMBER column (i.e., which row was added first?)

MINROW("Students", "GPA", ([Class] = "2018")) // returns the row for the (one) student with the lowest GPA of the class of 2018

MINROW("Sales", "Close Date", ([City] = "Madrid")) // returns the row for the oldest sale in Madrid

MINROW("Events", "Date", ([Date] > [_THISROW].[Date])) // returns the row for the most recent following event

MINROW("Events", "Date", ([Date] >= DATE("1/1/" & YEAR(TODAY())))) // returns the row for the earliest event this year

MINROW("MyTable", "SomeColumn", FALSE) // returns a blank value because the select-row? expression will always return FALSE, excluding all rows from the search.

Common Problems

MINROW(Events, Date) //produces the error, Expression [...] could not be parsed due to exception: #VALUE!. In this example, the column name, Date, has significance within the internals of AppSheet and causes confusion. Any data set name that matches an AppSheet or Excel function name may produce this problem. To fix, quote the problem name:

MINROW(Events, "Date")

MINROW("Events", "Date", ([Venue] = [Wanted Venue])) //produces the error, Unable to find column [...]. Column references within the select-row? expression (e.g., [Venue]) are to the row being considered as the data set is searched. To access columns outside the row being considered, such as when using MINROW() from within a column constraint, app formula, initial value, or format rule, reference the external column using _THISROW:

MINROW("Events", "Date", ([Venue] = [_THISROW].[Wanted Venue]))

**************

Sample Expression MAX()

MAXROW("Products", "Discount") // returns the key value for the row in the Products data set that has the highest value in the Discount column (i.e., which product has the greatest discount?). Equivalent to

MAXROW("Products", "Discount", TRUE).

MAXROW("MyTable", "_ROWNUMBER") // returns the key value for the row in the MyTable data set that has the highest value in the _ROWNUMBER column (i.e., which row was added last?).

MAXROW("Students", "GPA", ([Class] = "2018")) // returns the row for the (one) student with the highest GPA of the class of 2018.

MAXROW("Sales", "Close Date", ([City] = "Madrid")) // returns the row for the newest sale in Madrid.

MAXROW("Events", "Date", ([Date] < [_THISROW].[Date])) // returns the row for the most recent preceding event.

MAXROW("Events", "Date", ([Date] < DATE("1/1/" & YEAR(TODAY())))) // returns the row for the most recent event prior to this year.

MAXROW("MyTable", "SomeColumn", FALSE) // returns a blank value because the select-row? expression will always return FALSE, excluding all rows from the search.

Common Problems

MAXROW(Events, Date) //produces the error, Expression [...] could not be parsed due to exception: #VALUE!. In this example, the column name, Date, has significance within the internals of AppSheet and causes confusion. Any data set name that matches an AppSheet or Excel function name may produce this problem. To fix, quote the problem name

MAXROW(Events, "Date")

MAXROW("Events", "Date", ([Venue] = [Wanted Venue])) //produces the error, Unable to find column [...]. Column references within the select-row? expression (e.g., [Venue]) are to the row being considered as the data set is searched. To access columns outside the row being considered, such as when using MAXROW() from within a column constraint, app formula, initial value, or format rule, reference the external column using _THISROW:

MAXROW("Events", "Date", ([Venue] = [_THISROW].[Wanted Venue]))

**************

Sample Expression MOD()

MOD([Minutes], 60) // the number of minutes in excess of the whole hours represented by the Minutes column. Whole hours may be computed with [Minutes] / 60.

MOD([Hours], 24) // the number of hours in excess of the whole days represented by the Hours column. Whole days may be computed with [Hours] / 24.

MOD([Days], 7) // the number of days in excess of the whole weeks represented by the Days column. Whole weeks may be computed with [Days] / 7.

MOD(9, 7) // 2

MOD(-9, 7) // -2

MOD(9, -7) // 2

MOD(-9, -7) // -2

MOD(9, 0) // 0

MOD(9, 9) // 0

**************

Sample Expression NOT()

NOT(TRUE) // FALSE

NOT(FALSE) // TRUE

NOT(ISBLANK([Detail])) // TRUE if the Detail column value is not blank.

Equivalent to:

ISNOTBLANK([Detail])

NOT(WEEKDAY(TODAY()) = "Sunday") // TRUE if the today's weekday name is not Sunday.

Equivalent to

WEEKDAY(TODAY()) <> "Sunday"

NOT(IN([Color]), {"Orange", "Red"}) // TRUE if the Color column value is not Orange or Red.

Equivalent to

AND(([Color] <> "Orange"), ([Color] <> "Red"))

**************

Sample Expression NOW()

(NOW() - 1) //gives the date & time one day in the past.

Equivalent to

(NOW() - "024:00:00")

(NOW() + 1) //gives the date & time one day in the future.

(NOW() + 7) //gives the date & time one week in the future.

DATE(NOW()) //gives the current date as a Date value.

Equivalent to

TODAY()

TIME(NOW()) //gives the current time.

Equivalent to

TIMENOW()

IFS(

OR(

AND(

[_THISROW_BEFORE].[status_code]<>"Not Started", [status_code]<>"Not Started"

),

AND(

[_THISROW_BEFORE].[status_code]="Not Started", [status_code]="Not Started"

)

),[_THISROW_BEFORE].[act_start_date],

AND(

[_THISROW_BEFORE].[status_code]="Not Started",

[status_code]<>"Not Started"

),

NOW()-

((TIME( CONCATENATE( MOD(FLOOR([target_drtn_hr_cnt]), 24), ":",

(MOD(((60 * 60) * [target_drtn_hr_cnt]),

(60 * 60)) / 60), ":", MOD(((60 * 60) * [target_drtn_hr_cnt]), 60) ) )

- "00:00:00" ) + (FLOOR([target_drtn_hr_cnt] / 24) * 24))

)

**************

Sample Expression OR()

OR(FALSE, FALSE) // FALSE

OR(FALSE, TRUE) // TRUE

OR(TRUE, FALSE) // TRUE

OR(TRUE, TRUE) // TRUE

OR(([_THIS] < -2), ([_THIS] > 2))

// TRUE if the _THIS column value is outside the range of -2 to 2.

OR(([Color] = "Red"), ([Color] = "Yellow"), ([Color] = "Green")) // TRUE if the Color column value is any of Red, Yellow, or Green.

Equivalent to

IN([Color], {"Red" "Yellow", "Green"})

Allow Access

//Allow access, e.g. to a view (by Show if), column (by Show_If), or table (in an Are updates allowed? expression) by time of day, day of week, or if the app user has special access:

OR(

 AND((TIMENOW() >= "9:00 AM"), (TIMENOW() < "5:00 PM")),

 IN(WEEKDAY(TODAY()), {"Saturday", "Sunday"}),

 IN(

   USEREMAIL(),

   SELECT(Users[Email], [Special Access?], TRUE)

 )

)

/*

1) OR(..., ..., ...) returns TRUE (grants access) if any of the conditions are TRUE.

2) AND((TIMENOW() >= "9:00 AM"), (TIMENOW() < "5:00 PM")) returns TRUE if the current time is between 9:00 AM and 5:00 PM. Alternatively: IN(HOUR(TIMENOW() - "00:00:00"), {9, 10, 11, 12, 13, 14, 15, 16}).

3) IN(WEEKDAY(TODAY()), {"Saturday", "Sunday"}) returns TRUE if the current weekday is either Saturday or Sunday. Alternatively: OR((WEEKDAY(TODAY()) = "Saturday"), (WEEKDAY(TODAY()) = "Sunday")).

4) IN(USEREMAIL(), ...) returns TRUE if the current user's email address occurs in the specified list.

5) SELECT(Users[Email], [Special Access?], TRUE) provides a list of unique email addresses who are to be granted special access according to the Users table.*/

OR(([Price]*[Quantity])>$10,000.00,[Price]>$100.00)

(CONTAINS([Fruit],"Oranges"),CONTAINS([Fruit],"Apples"),CONTAINS([Fruit],"Bananas"))

**************

Sample Expression RANDBETWEEN()

RANDBETWEEN(1, 10) //gives a random whole number between 1 and 10.

(RANDBETWEEN(0, 1) = 1) //gives a random Yes/No value.

(RANDBETWEEN(0, 100) / 100.0) //gives a random Decimal value between 0.00 and 1.00 with two digits after the decimal point

INDEX({"Heads", "Tails"}, RANDBETWEEN(1, 2)) //returns Heads or Tails as a Text value.

MID("ABCDE", RANDBETWEEN(1, 5), 1) //returns a random letter from A to E as a Text value.

INDEX(Students[Name], RANDBETWEEN(1, COUNT(Students[Name]))) //returns a randomly-chosen student's name.

ORDERBY(Cards[Card ID], RANDBETWEEN(1, 52)) //returns the List of Card ID column values in a random order.

**************

Sample Expression RIGHT()

RIGHT("123 Maple Dr", 5) // le Dr

RIGHT("123 Maple Dr", 0) //returns blank.

Fractional Component of Decimal Value

//The fractional component of a Decimal value in the Result column:

NUMBER(

 RIGHT(

   TEXT([Result]),

   (LEN(TEXT([Result]))

     - FIND(".", TEXT([Result])))

 )

)

/*

1) TEXT([Result]) converts the Decimal value to a Text value. The textual functions used in this example interpret non-textual values differently. Using TEXT() ensures the Result column value is interpreted the same by each function.

2) FIND(".", ...) locates the decimal point in the value.

3) (LEN(...) - FIND(...)) calculates the length of the fractional component by subtracting the length of the non-fraction part from the entire length.

4) RIGHT(..., ...) extracts the fractional part.

5) NUMBER(...) converts the extracted text to a Number.*/

Street Name from Street Address

//Everything after the first word of a street address, which is typically the street name:

RIGHT([Address], (LEN([Address]) - FIND(" ", [Address])))

/*

1) FIND(" ", [Address]) locates the first/leftmost space in the Address column value.

2) LEN([Address]) - FIND(...) calculates the length of the Address column value beyond the first space by subtracting the length of the text preceding the first space from the total length.

3) RIGHT([Address], ...) returns the rightmost text following the first space.*/

**************

Sample Expression ROUND()

ROUND(3.14) // 3

ROUND(-3.14) // -3

ROUND(98.6) // 99

ROUND(5) // 5

**************

Sample Expression TIMENOW()

TIMENOW() //is equivalent to

TIME(NOW())

(TIMENOW() - 1) //gives the time one hour in the past.

(TIMENOW() + 1) //gives the time one hour in the future.

(TIMENOW() + 24) //gives the time one day in the future.

**************

Sample Expression SELECT()

SELECT(Students[First Name], TRUE) // A list of first names (possibly with duplicates) of all students.

//Equivalent to

Students[First Name]

SELECT(Students[First Name], ([Class of] = "2020"), FALSE) // A list of first names (possibly with duplicates) of the students of the class of 2020. Equivalent to:

SELECT(Students[First Name], ([Class of] = "2020"))

SELECT(Students[First Name], ([Class of] = "2020"), TRUE) // A list of distinct first names (duplicates omitted) of the students of the class of 2020.

SELECT(Orders[Order ID], ([Customer] = [_THISROW].[Customer])) //returns the Order ID column values (the row keys) for rows in the Orders data set in which the Customers column value is equal to the Customers column value of the current form (i.e., orders for this customer). Equivalent to

FILTER("Orders", ([Customer] = [_THISROW].[Customer]))

SELECT(Products[Name], ([Price] < 100), TRUE) //returns the distinct names of products priced less than $100.

Troubleshooting

//Within the second argument, the select-row? expression, any column references are interpreted from the perspective of the data set being searched, not that of the data set from which the expression is run. In order to reference columns from the current row, you must dereference _THISROW.

//For example, consider this attempt from an order row to get the item descriptions from the order detail rows:

SELECT(Order Details[Description], ([Order ID] = [Order ID]), TRUE)

//The goal is to select rows from the Order Details data set with an Order ID column value that matches this order's own ID. To reference a column of the current row, dereference _THISROW to get the desired column:

SELECT(Order Details[Description], ([Order ID] = [_THISROW].[Order ID]), TRUE)

**************

Sample Expression SUBSTITUTE()

SUBSTITUTE("abc", "a", "A") // Abc

SUBSTITUTE("+1 212-555-1212", "-", ".") // +1 212.555.1212

SUBSTITUTE("Hello, {}!", "{}", "Bob") // Hello, Bob!

**************

Sample Expression SUM()

SUM(Classes[Seat Count]) // The sum of all values in the Seat Count column of the Classes table. Equivalent to:

SUM(SELECT(Classes[Seat Count], TRUE))

SUM([Discounts]) // The sum of the items in the Discounts column value, where Discounts is of type List.

SUM(LIST(1, 2, 3)) //returns Number: 6

Sum Values from Select Rows

//Compute the total delivery charges recorded within a reporting period:

SUM(

 SELECT(

   Deliveries[DeliveryCharge],

   AND(

     ([DateDone] >= [_THISROW].[BeginDate]),

     ([DateDone] < [_THISROW].[EndDate])

   )

 )

)

/*

1) SELECT(Deliveries[DeliveryCharge], ...) gets a list of delivery charges from select rows in the Deliveries table.

2) AND(..., ...) limits the SELECT() results to only those rows that match all of the conditions.

3) ([DateDone] >= [_THISROW].[BeginDate]) limits the count to only rows with a DateDone no earlier than the report's BeginDate.

4) ([DateDone] < [_THISROW].[EndDate]) further limits the rows to those with dates before the report's end date.

5) SUM(...) totals the values from the result of the SELECT().*/

Common Problems

SUM(1, 2, 3) // the arguments are not in list form. To fix, wrap them in LIST() to construct a list:

SUM(LIST(1, 2, 3))

***************

Sample Expression SPLIT()

SPLIT("John Smith", " ") //produces a two-item list containing John and Smith.

SPLIT(LIST("Banana, Apricot, Grapes"), ", ") //produces a three-item list: Banana, Apricot, Grapes

***************

Sample Expression UNIQUE()

UNIQUE(LIST(1, 1, 2, 1, 3)) // 1, 2, 3

UNIQUE(LIST("Mary", "David", "Joe", "David")) // Mary, David, Joe

UNIQUE(Products[Color]) //returns a list of Color column values from the Products table without duplicates.

**************

Sample Expression TEXT()

One-Argument Form

TEXT(4096) (Number) // 4,096

TEXT("4096") (Text) // 4096

TEXT(32768.1024) (Decimal) // 32,768.10

TEXT("2019-11-01 13:34") //(DateTime): 11/1/2019 1:34:00 PM

TEXT([price]) //(Price) : $0.41

TEXT([percent]) //(Percent): 41%

Two-Argument Form

TEXT("2019-11-01", "dd/mm/yyyy") // 01/11/2019

TEXT("2019-11-01 1:34 PM", "HH:MM")  // 13:34

TEXT("2:23", "H") // 2

TEXT("2:23 PM", "H") // 14

TEXT("14:23", "H") // 14

TEXT("14:23", "H A/P") // 2 P

TEXT("14:23", "H AM/PM") // 2 PM

TEXT("4/15/2020 14:23", "M") // 4

TEXT("4/15/2020 14:23", "H:M") // 14:23

**************

Sample Expression TIME()

TIME("3:14") //(24-hour time if no AM/PM) // 3:14:00 AM

TIME("15:14") //(24-hour time) // 3:14:00 PM

TIME("3:14 PM") //(12-hour time with AM/PM) // 3:14:00 PM

TIME(TIMENOW()) // The current time from a Time value

TIME("4/1/2010") // 12:00:00 AM (a Date value has no time component, so a default is used).

TIME(TODAY()) // 12:00:00 AM (TODAY() returns a Date value, which has no time component, so a default is used).

TIME("4/1/2010 3:14") // 3:14:00 AM

TIME(NOW()) // The current time from a DateTime value

Duration from Decimal Hours

//Convert a number of hours expressed as a Decimal value to a Duration value.

(

 TIME(

   CONCATENATE(

     MOD(FLOOR([Hours]), 24),

     ":",

     (MOD(((60 * 60) * [Hours]), (60 * 60)) / 60),

     ":",

     MOD(((60 * 60) * [Hours]), 60)

   )

 )

 - "00:00:00"

)

+ (FLOOR([Hours] / 24) * 24)

/*

1) (60 * 60) gives number of seconds in 60 minutes (one hour).

2) (60 * 60) * [Hours] gives number of seconds in the time period expressed by the Hours column value.

3) FLOOR([Hours] / 24) gives whole days.

4) MOD(FLOOR([Hours]), 24) gives remaining whole hours.

5) (MOD(((60 * 60) * [Hours]), (60 * 60)) / 60) gives remaining whole minutes.

6) MOD(((60 * 60) * [Hours]), 60) gives remaining seconds.

7) CONCATENATE(..., ":", ..., ":", ...) constructs a Text value formatted as a time value. Note that this only includes the time within one day; Time values cannot exceed 24 hours.

8) TIME(...) converts the Text value to a Time value.

9) (... - "00:00:00") subtracts a Time value from a Time value, giving a Duration value. A Duration value allows more than 24 hours.

10) ... + (FLOOR([Hours] / 24) * 24) adds the number of hours in the whole days from Hours.*/

Common Problems

TIME("Good morning, Martin!") // Returns blank because the textual input isn't a recognized temporal type and so cannot be converted.

**************

Sample Expression TODAY()

(TODAY() - 1) //gives yesterday's date.

(TODAY() + 1) //gives tomorrow's date.

(TODAY() + 7) //gives the date one week from today.

(TODAY() - WEEKDAY(TODAY())) //gives the date of Saturday of last week

(TODAY() - WEEKDAY(TODAY()) + 4) //gives the date of Wednesday of this week

SWITCH(WEEKDAY(EOMONTH(TODAY(),-1)+1),

1,IFS(DAY(EOMONTH(TODAY(),0))>28,DAY(EOMONTH(TODAY(),0))-29),

5,IFS(DAY(EOMONTH(TODAY(),0))<31,DAY(EOMONTH(TODAY(),0))-28),

6,IFS(DAY(EOMONTH(TODAY(),0))>28,1),

7,IFS(DAY(EOMONTH(TODAY(),0))>30,1), DAY(EOMONTH(TODAY(),0))-28)

//number of working days in a month.

**************

Sample Expression TOP()

TOP(LIST("Red", "Yellow", "Green"), 2) //returns a list of 2 items: Red , Yellow

TOP({"Red", "Yellow", "Green"}, 4) //returns a list of 3 items: Red , Yellow , Green

Days of the Month

A list of days of a month, accounting for the difference in months:

TOP(

 {

   1, 2, 3, 4, 5, 6, 7, 8, 9,

   10, 11, 12, 13, 14, 15, 16, 17, 18, 19,

   20, 21, 22, 23, 24, 25, 26, 27, 28, 29,

   30, 31

 },

 EOMONTH([Date], 0)

)

/*

1) {1, 2, 3, ..., 31} constructs a list of possible month day numbers.

2) EOMONTH([Date], 0) identifies the number of the last day of the month specified by the Date (or DateTime) value in the Date column.

3) TOP(..., ...) returns a list of only the day numbers from 1 to the last day of the given month.*/

Lowest Values

//The 5 earliest employee hire dates:

TOP(SORT(Employees[Hire Date]), 5)

/*

1) Employees[Hire Date] retrieves the list of all values from the Hire Date column of the Employees table.

2) SORT(...) orders the list of dates chronologically in ascending/low-to-high order (the default sort order), putting the earliest dates at the beginning.

3) TOP(..., 5) returns the first 5 values from the sorted list, the 5 earliest hire dates.*/

Rows with Highest Values

//The rows of the 3 students with the highest GPAs in Mr Sandwich's class:

TOP(

 ORDERBY(

   FILTER("Students",

     AND(

       ISNOTBLANK([Teacher]),

       ([Teacher] = "Mr Sandwich")

     )

   ),

   [GPA], TRUE

 ),

 3

)

/*

1) FILTER("Students", ...) returns a list of key values from the Students table that match a condition.

2) AND(..., ...) limits the filter to only those rows that match all of the given sub-conditions.

3) ISNOTBLANK([Teacher]) requires the Teacher column value not be blank.

4) [Teacher] = "Mr Sandwich" requires the Teacher column value be exactly Mr Sandwich.

5) ORDERBY(..., [GPA], TRUE) orders the filtered keys by the values of their corresponding GPA column value in descending/high-to-low order (TRUE), putting high GPAs first.

6) TOP(..., 3) returns the first 3 items in the ordered list, the keys of the rows having the 3 highest GPAs*/

**************

Sample Expression TOTALHOURS()

TOTALHOURS("000:00:18") // 0.005 (number of hours in 18 seconds)

TOTALHOURS("000:15:00") // 0.25 (number of hours in 15 minutes)

TOTALHOURS("024:00:00") // 24

TOTALHOURS("024:00:00" + "000:15:00" + "000:00:18") //returns 24.255;

equivalent to

TOTALHOURS(SUM(LIST("024:00:00", "000:15:00", "000:00:18")))

TOTALHOURS(TIMENOW() - "00:00:00")

//returns the number of hours that have passed from midnight today to now. TIMENOW() returns the current Time; subtracting one Time value ("00:00:00") from another gives the needed Duration argument.

Hours Worked This Week

TOTALHOURS(

 SUM(

   SELECT(

     Timesheets[Hours],

     AND(

       ([Employee] = USEREMAIL()),

       ([Date Worked] > (TODAY() - WEEKDAY(TODAY()))),

       ([Date Worked] <= (TODAY() - WEEKDAY(TODAY())) + 7)

     )

   )

 )

)

/*

1) SELECT(Timesheets[Hours], ...) gathers the Duration-type values of the Hours column from rows in the Timesheets table that match the given criteria (... ; see (2))

2) AND(..., ..., ...) includes only rows that match all of the given criteria (see (3), (4), and (5))

3) ([Employee] = USEREMAIL()) includes only rows with an Employee column value that matches the current app user's email address

4) ([Date Worked] > (TODAY() - WEEKDAY(TODAY()))) includes only rows for which the Date Worked is after the last day of the previous week

5) ([Date Worked] <= (TODAY() - WEEKDAY(TODAY())) + 7) includes only rows for which the Date Worked is on or before the last day of the the current week.

6) SUM(...) computes the total time as a Duration value from the gathered daily Hours values from (1)

TOTALHOURS(...) converts the Duration value from (6) to a Decimal value.*/

*************

Sample Expressions UPPER()

UPPER("AppSheet") : APPSHEET

**************

Sample Expression WEEKDAY()

WEEKDAY("1/1/2019") // 3

INDEX(LIST("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"), WEEKDAY("1/1/2019")) //(Text): Tue

WEEKDAY(TODAY()) // Today's numeric day of the week

(TODAY() - WEEKDAY(TODAY())) // Date of Saturday last week.

(TODAY() - WEEKDAY(TODAY()) + 6) // Date of Friday this week.

(TODAY() - WEEKDAY(TODAY()) + 7 + 4) // Date of Wednesday next week.

([Weekday] - WEEKDAY(TODAY())) // Offset of this week's [Weekday] (1 = Sunday, ..., 7 = Saturday) from today (e.g., 0 = today, -1 = yesterday, 1 = tomorrow).

(TODAY() + ([Weekday] - WEEKDAY(TODAY()))) //or (perhaps more intuitively)

(TODAY() - WEEKDAY(TODAY()) + [Weekday])// Date of [Weekday] within the current week.

([Date] + ([Weekday] - WEEKDAY([Date]))) //or

([Date] - WEEKDAY([Date]) + [Weekday]) // Date of [Weekday] within a week containing [Date].

MOD(([Weekday] - WEEKDAY([Date]) + 7), 7) // Offset of the [Weekday] on or after [Date]

MOD(([Weekday] - WEEKDAY([Date]) - 7), 7) // Offset of the [Weekday] before or on [Date].

([Date] + MOD(([Weekday] - WEEKDAY([Date]) + 7), 7)) // Date of the first [Weekday] on or after [Date].

([Date] + MOD(([Weekday] - WEEKDAY([Date]) - 7), 7)) // Date of the first [Weekday] before  or on [Date].

([Date] - DAY([Date]) + 1) or (EOMONTH([Date], -1) + 1) // Date of the first of the month containing [Date]

(([Date] - DAY([Date]) + 1) + MOD(([Weekday] - WEEKDAY([Date] - DAY([Date]) + 1) + 7), 7)) // Date of the first [Weekday] of the month containing [Date].

(EOMONTH([Date], 0) + MOD(([Weekday] - WEEKDAY(EOMONTH([Date], 0)) - 7), 7)) // Date of the last [Weekday] of the month containing [Date]

(

(

([Date] - DAY([Date]) + 1)

+

MOD(([Weekday] - WEEKDAY([Date] - DAY([Date]) + 1) + 7), 7))

+

(([Ordinal] - 1) * 7

)

)

// Date of the [Ordinal]-th [Weekday] of the month containing [Date]

**************

Sample Expression WORKDAY()

WORKDAY(TODAY(), 2) // The date two business days in the future. If today is a Wednesday, the date would be Friday; if today is Thursday, the date would be Monday; if today is Friday, the date would be Tuesday.

WORKDAY(TODAY(), 5) // Five business days (one business week) in the future.

WORKDAY(TODAY(), -5) // Five business days (one business week) in the past.

WORKDAY("12/1/2019", 25, LIST("12/24/2019", "12/25/2019", "1/1/2020")) // 1/8/2020

WORKDAY([Project Start], [Est Days], Holidays[Date]) // Computes a project's anticipated end date from its known start date and an estimated number of days of work, taking into account known holidays.

**************

Sample Expression WEEKNUM()

WEEKNUM("1/1/2019") // 1

WEEKNUM("12/31/2019") // 53

WEEKNUM(TODAY()) // Today's week number

‍