Aplikasi Kasir Bengkel & Sparepart (AppSheet)

BAGIAN 1: DATABASE (GOOGLE SHEETS)

Buat file Google Sheet

1. Sheet: Mekanik
   • ID_Mekanik
   • Nama
   • Kontak
   • TipeKomisi
   • NilaiKomisi
   • Status
2. Sheet: Produk
   • ID_Produk
   • NamaProduk
   • Satuan
   • HargaBeli
   • HargaJual
   • TypeKendaraan
   • MerekKendaraan
   • Gambar
3. Sheet: Jasa
   • ID_Jasa
   • NamaJasa
   • HargaJasa
4. Sheet: Pelanggan
   • ID_Pelanggan
   • Nama
   • Telepon
   • Alamat
   • TypeKendaraan
   • MerekKendaraan
   • PlatNomor
5. Sheet: Transaksi
   • ID_Transaksi
   • Tanggal
   • ID_Pelanggan
   • JenisOrder
   • ID_Mekanik
   • TotalBelanja
   • TotalJasa
   • TotalHPP
   • NominalBayar
   • Kembalian
   • Status
   • Catatan
   • User_Shift
6. Sheet: TransaksiDetail
   • ID_Detail
   • ID_Transaksi
   • ItemType
   • Produk
   • Jasa
   • Qty
   • HargaSaat
   • HPPSaat
   • Subtotal
7. Sheet: StokMutasi
   • ID_Mutasi
   • Tanggal
   • ID_Produk
   • Tipe
   • Qty
   • Keterangan
   • ID_Referensi
8. Sheet: Pengeluaran
   • ID_Pengeluaran
   • Tanggal
   • Kategori
   • Nominal
   • Keterangan
9. Sheet: Pengguna
   • Username
   • Password
   • Role
10. Sheet: InfoToko
    • ID
    • NamaToko
    • Alamat
    • Telp
    • FooterStruk
11. Sheet: MenuApp
    • ID_Menu
    • TargetView
    • Gambar

BAGIAN 2: PENGATURAN DATA (APPSHEET)

Impor semua tabel ke AppSheet. Lalu buka menu Data -> Columns.

A. Pengaturan Login (USER SETTINGS)

Masuk ke menu Data -> tab User Settings.

1. Column 1:
   • Name: Username
   • Type: Enum
   • Values: ADMIN, KASIR
   • Show: ON
   • Data Validity -> Require: ON
   • Update Behavior -> Editable? : ON
   • Display -> Label: ON
2. Column 2:
   • Name: Password
   • Type: Text
   • Show: ON
   • Data Validity -> Valid if :
     Copy Code

     LOOKUP(USERSETTINGS("Username"),"PENGGUNA","Role","Password")
     = [Password]

   • Invalid value error : pasword salah!
   • Require: ON
   • Update Behavior -> Editable? : ON
3. Simpan.

B. Tabel: Pengguna

1. Username: Type Text, Key (Centang), Label (Centang).
2. Password: Type Text.
3. Role: Type Enum (Values: ADMIN, KASIR).

C. Tabel: Mekanik

1. ID_Mekanik: Type Text, Key (Centang), Label (UnCentang), Initial Value:
   Copy Code

   UNIQUEID()

2. Nama: Type Name, Label (Centang).
3. Kontak: Type Phone.
4. TipeKomisi: Type Enum (Values: Persen, Nominal).
5. NilaiKomisi: Type Decimal
6. Status: Type Enum (Values: Aktif, Nonaktif).

D. Tabel: Produk

1. ID_Produk: Type Text, Key (Centang), Label (UnCentang), Initial Value:
   Copy Code

   UNIQUEID()

2. NamaProduk: Type Text, Label (Centang).
3. Satuan: Type Enum (Pcs, Botol, Set, Lembar,Liter,Kilo).
4. HargaBeli: Type Price Currency: Rp
5. HargaJual: Type Price Currency: Rp.
6. TypeKendaraan: Type Enum (Values: Mobil, Motor, Umum).
7. MerekKendaraan: Type Enum (Allow Other Values: ON).
   • Valid_If:
     Copy Code

     IFS(
       [TypeKendaraan] = "Mobil",
     LIST("Toyota", "Daihatsu", "Honda",
     "Suzuki", "Mitsubishi", "Isuzu",
     "Hino", "Hyundai", "Wuling", "BYD",
     "Chery", "BMW", "Mazda"),
       [TypeKendaraan] = "Motor",
     LIST("Honda", "Yamaha", "Suzuki",
     "Kawasaki", "Vespa", "Viar", "Kaisar",
     "Kanzen", "Listrik"),
       [TypeKendaraan] = "Umum",
     LIST("Umum")
     
     )

8. Gambar: Type Image.
9. Virtual Column: Stok Awal.
   • App Formula:
     Copy Code

     SUM(SELECT(StokMutasi[Qty], AND([ID_Produk]=[_THISROW].[ID_Produk],
     [Tipe]="Masuk"))) - SUM(SELECT(StokMutasi[Qty],
     AND([ID_Produk]=[_THISROW].[ID_Produk], [Tipe]="Keluar")))

   • Type: Number.
10. Virtual Column: Sisa Stok.
    • App Formula:
      Copy Code

      [Stok Awal] - SUM([Related
      TransaksiDetails][Qty])

    • Type: Number.

E. Tabel: Jasa

1. ID_Jasa: Type Text, Key (Centang), Label (UnCentang), Initial Value:
   Copy Code

   UNIQUEID()

2. NamaJasa: Type Text, Label (Centang).
3. HargaJasa: Type Price Currency: Rp.

F. Tabel: Pelanggan

1. ID_Pelanggan: Type Text, Key (Centang), Label (UnCentang), Initial Value:
   Copy Code

   UNIQUEID()

2. Nama: Type Name, Require? (Centang). Initial Value: "Pelanggan".
3. Telepon: Type Phone.
4. Alamat: Type LongText.
5. TypeKendaraan: Type Enum (Values: Mobil, Motor).
6. MerekKendaraan: Type Enum (Allow Other Values: ON).
   • Valid_If:
     Copy Code

     IFS(
       [TypeKendaraan] = "Mobil",
     LIST("Toyota", "Daihatsu", "Honda",
     "Suzuki", "Mitsubishi", "Isuzu",
     "Hino", "Hyundai", "Wuling", "BYD",
     "Chery", "BMW", "Mazda"),
       [TypeKendaraan] = "Motor",
     LIST("Honda", "Yamaha", "Suzuki",
     "Kawasaki", "Vespa", "Viar", "Kaisar",
     "Kanzen", "Listrik")
     
     )

7. PlatNomor: Type Text, Require? (Centang).

G. Tabel: Transaksi

1. ID_Transaksi: Type Text, Key (Centang), Initial Value:
   Copy Code

   UNIQUEID()

2. Tanggal: Type DateTime, Initial Value:
   Copy Code

   NOW()

3. ID_Pelanggan: Type Ref (Source Table: Pelanggan).
   • Show?:
     Copy Code

     [JenisOrder]="Servis"

   • Require?:
     Copy Code

     [JenisOrder]="Servis"

   • Initial Value:
     Copy Code

     IF([JenisOrder]="Penjualan", "UMUM",
     "")

4. JenisOrder: Type Enum (Values: Servis, Penjualan), Initial Value: "Servis".
5. ID_Mekanik: Type Ref (Source Table: Mekanik).
   • Show?:
     Copy Code

     [JenisOrder]="Servis"

   • Require?:
     Copy Code

     [JenisOrder]="Servis"

6. TotalBelanja: Type Price. Currency: Rp.
   • App Formula:
     Copy Code

     SUM([Related
     TransaksiDetails][Subtotal])

7. TotalJasa: Type Price. Currency: Rp.
   • App Formula:
     Copy Code

     SUM(SELECT([Related
     TransaksiDetails][Subtotal], [ItemType]="Jasa"))

8. TotalHPP: Type Price. Currency: Rp.
   • App Formula:
     Copy Code

     SUM([Related
     TransaksiDetails][TotalModal])

9. NominalBayar: Type Price. Currency: Rp.
   • Show If:
     Copy Code

     OR([Status]="Selesai",
     [Status]="Penjualan")

10. Kembalian: Type Price. Currency: Rp.
    • Show If:
      Copy Code

      OR([Status]="Selesai",
      [Status]="Penjualan")

    • App Formula:
      Copy Code

      [NominalBayar]
      - SUM([Related TransaksiDetails][Subtotal])

11. Status: Type Enum (Values: Proses, Selesai, Penjualan, Batal).
    • Initial Value:
      Copy Code

      IF([JenisOrder]="Penjualan", "Penjualan",
      "Proses")

    • Update Behavior -> Editable?:
      Copy Code

      AND([Status] <> "Selesai", [Status] <>
      "Penjualan")

12. User_Shift: Type Text, App Formula :
    Copy Code

    UserSettings("Username")

    , Editable?: OFF.
13. Virtual Column: KeuntunganBersih.
    • App Formula:
      Copy Code

      [TotalBelanja] - [TotalHPP]

    • Type: Price.
14. Virtual Column: KomisiMekanikHitung.
    • Type: Price
    • .App Formula:
      Copy Code

      IFS( ISBLANK([ID_Mekanik]), 0, [ID_Mekanik].[TipeKomisi] = "Nominal",
      [ID_Mekanik].[NilaiKomisi], [ID_Mekanik].[TipeKomisi] = "Persen",
      [TotalJasa] * ([ID_Mekanik].[NilaiKomisi] / 100) )

15. Virtual Column: hari.
    • Type: date
    • .App Formula:
      Copy Code

      DATE([Tanggal])

16. Virtual Column: bulan.
    • Type: Text
    • .App Formula:
      Copy Code

      TEXT([Tanggal], "MM-YY")

17. Virtual Column: tahun.
    • Type: Number
    • .App Formula:
      Copy Code

      YEAR([Tanggal])

H. Tabel: TransaksiDetail

1. ID_Detail: Type Text, Key (Centang), Initial Value:
   Copy Code

   UNIQUEID()

2. ID_Transaksi: Type Ref (Source Table: Transaksi), Is a part of?: ON.
3. ItemType: Type Enum (Values: Produk, Jasa).
   • Initial Value:
     Copy Code

     IF([ID_Transaksi].[JenisOrder]="Penjualan",
     "Produk", "Jasa")

   • Valid_If:
     Copy Code

     IF([ID_Transaksi].[JenisOrder]="Penjualan",
     LIST("Produk"), LIST("Produk", "Jasa"))

   • Editable?:
     Copy Code

     [ID_Transaksi].[JenisOrder] <> "Penjualan"

4. Produk : Type Ref (Source Table: Produk).
   • Show? :
     Copy Code

     [ItemType] = "Produk"

5. Jasa: Type Ref (Source Table: Jasa).
   • Show? :
     Copy Code

     [ItemType] = "Jasa"

6. Qty: Type Number, Initial Value: 1.
   • Valid_If:
     Copy Code

     IF([ItemType]="Produk", [Qty] <= [Produk].[sisa
     stok], TRUE)

   • Error Msg: "Stok tidak cukup!"
7. HargaSaat: Type Price.
   • Initial Value:
     Copy Code

     IFS(
     [ItemType] = "Produk", [Produk].[HargaJual], [ItemType] = "Jasa",   [Jasa].[HargaJasa] )

8. HPPSaat: Type Price.
   • Initial Value:
     Copy Code

     IF([ItemType]="Produk", [Produk].[HargaBeli], 0)

9. Subtotal: Type Price, App Formula:
   Copy Code

   [Qty] * [HargaSaat]

   .
10. Virtual Column: TotalModal.
    • App Formula:
      Copy Code

      [Qty] * [HPPSaat]

    • Type: Price.
11. Virtual Column: NamaItem_Cetak (Kolom Baru untuk Web Print).
    • App Formula:
      Copy Code

      IFS(
      [ItemType] = "Produk", [Produk].[NamaProduk], [ItemType] = "Jasa",   [Jasa].[NamaJasa] )

    • Type: Text

I. Tabel: StokMutasi

1. ID_Mutasi: Type Text, Key (Centang), Initial Value:
   Copy Code

   UNIQUEID()

2. Tanggal: Type DateTime, Initial Value:
   Copy Code

   NOW()

3. ID_Produk: Type Ref (Source Table: Produk).
4. Tipe: Type Enum (Values: Masuk, Keluar).
5. Qty: Type Number.
6. Keterangan: Type Text.
7. ID_Referensi: Type Text, Editable?: OFF.

J. Tabel: MenuApp

1. ID_Menu: Type Text, Key (Centang). Label (UnCentang).
2. TargetView: Type Text, Label (Centang).
3. Gambar: Type Image.

K. Tabel: InfoToko

1. ID: Type Number, Key (Centang).
2. NamaToko: Type Text.
3. Alamat: Type Text.
4. Telp : Type Text.
5. FooterStruk: Type Text.

L. Tabel: Pengeluaran

1. ID_Pengeluaran: Type Text, Key (Centang), Initial Value:
   Copy Code

   UNIQUEID()

2. Tanggal: Type DateTime, Initial Value:
   Copy Code

   NOW()

3. Kategori: Type Enum (Values: Listrik, Air, Gaji, Lainnya).
4. Nominal: Type Price.
5. Keterangan: Type Text.

BAGIAN 3: UX (TAMPILAN)

A. Buat SLICE (Data -> Slices)

1. Slice_Antrian:
   • Source Table: Transaksi
   • Row Filter Condition:
     Copy Code

     AND([Status]="Proses",
     [JenisOrder]="Servis")

2. Slice_Penjualan:
   • Source Table: Transaksi
   • Row Filter Condition:
     Copy Code

     AND([Status]="Penjualan",
     [JenisOrder]="Penjualan")

3. Slice_Riwayat:
   • Source Table: Transaksi
   • Row Filter Condition:
     Copy Code

     OR([Status]="Selesai",
     [Status]="Penjualan")

B. Buat VIEW (UX -> Views)

Isi database menuapp dengan

Buat Semua TargetView Untuk Nama View Semua view ini posisinya wajibRef (agar tidak muncul di menu bawah, akses lewat Home).

1. View ANTRIAN:
   • Data -> Slice ->create new slice
   • Slice Name : Slice_Antrian
   • Source Table : Transaksi
   • Row filter condition :
     Copy Code

     AND([Status]="Proses",
     [JenisOrder]="Servis")

   • Slice Columns : atur sesuai keinginan yang di tampilkan
   • Save
   • UX -> New View
   • View name:ANTRIAN
   • For this data: Slice_Antrian
   • View type: Deck
   • Position: Ref
   • Header: ID_Pelanggan
   • Secondary Header: ID_Mekanik
   • Summary column : TotalBelanja
   • Save
2. View PENJUALAN:
   • Data -> Slice ->create new slice
   • Slice Name : Slice_Penjualan
   • Source Table : Transaksi
   • Row filter condition :
     Copy Code

     AND([Status]="Penjualan",
     [JenisOrder]="Penjualan")

   • Slice Columns : atur sesuai keinginan yang di tampilkan
   • Update mode: read Only
   • Save
   • UX -> New View
   • View name:PENJUALAN
   • For this data: Slice_Penjualan
   • View type: Deck
   • Position: Ref
   • Header: TotalBelanja
   • Sub-Header: Tanggal
   • Summary column : User_Sift
   • Save
3. View RIWAYAT:
   • Data -> Slice ->create new slice
   • Slice Name : Slice_Riwayat
   • Source Table : Transaksi
   • Row filter condition :
     Copy Code

     [Status]="Selesai"

   • Slice Columns : atur sesuai keinginan yang di tampilkan
   • Update mode: read Only
   • Save
   • UX -> New View
   • View name:RIWAYAT
   • For this data: Slice_Riwayat
   • View type: Deck
   • Position: Ref
   • Header: ID_Mekanik
   • Sub-Header: TotalBelanja
   • Summary column : User_Sift
   • Save
4. View DAFTAR PRODUK: Source Produk, Type Table, Position Ref.
   • View name:DAFTAR PRODUK
   • For this data: Produk
   • View type: Deck
   • Position: Ref
   • Header: NamaProduk
   • Sub-Header: Sisa Stok
   • Summary column : HargaJual
   • Save
5. View DATA MEKANIK: Source Mekanik, Type Table, Position Ref.
   • View name:DATA MEKANIK
   • For this data: Mekanik
   • View type: Deck
   • Position: Ref
   • Header: Nama
   • Sub-Header: Kontak
   • Summary column : Status
   • Save
6. View DAFTAR JASA: Source Jasa, Type Table, Position Ref.
   • View name:DAFTAR JASA
   • For this data: Jasa
   • View type: Table
   • Position: Ref
   • Column order : NamaJasa, HargaJasa
   • Save
7. View INPUT PENGELUARAN
   • View name:INPUT PENGELUARAN
   • For this data: Pengeluaran
   • View type: Table
   • Position: Ref
   • Sort by : Tangal dan Desc
   • Save
8. View LAPORAN
   • Buat View
   • View name: Grafik_Omset
   • For this data: Slice_Riwayat
   • View type: Chart
   • Position: Ref
   • Chart type: Histogram
   • Group aggregate : SUM::TotalBelanja
   • Chart columns: Hari
   • Save
   • Buat View lagi
   • View name: Grafik_Laba
   • For this data: Slice_Riwayat
   • View type: Chart
   • Position: Ref
   • Chart type: Histogram
   • Group aggregate : SUM::KeuntunganBersih
   • Chart columns: Hari
   • Save
   • Buat View lagi
   • View name: Gaji
   • For this data: Slice_Riwayat
   • View type: Table
   • Position: Ref
   • Group by: ID_Mekanik, Tahun, Bulan, Hari
   • Group aggregate : SUM::KomisiMekanikHitung
   • Column order: Sesuaikan yang ingin di tampilkan
   • Save
   • Buat View lagi untuk menampilkan 3 laporan tadi
   • View name:LAPORAN
   • View type: Dashboard
   • Position: Ref
   • View entries: Grafik_Omset, Grafik_Laba, Gaji
   • Save
9. View DATA PELANGGAN (Form Khusus Toko):
   • View name:DATA PELANGGAN
   • For this data: Pelanggan
   • View type: Deck
   • Position: Ref
   • Header: Nama
   • Sub-Header: Alamat
   • Summary column : PlatNomor
   • Save
10. View INFO TOKO (Form Khusus Bayar):
    • View name:INFO TOKO
    • For this data: InfoToko
    • View type: Detail
    • Position: Middle
    • Header columns: NamaToko
    • Save

C. Menu Utama (HOME DASHBOARD)

1. Buat View HOME.
   • View name:HOME
   • For this data: MenuApp
   • View type: Gallery
   • Position: Next
   • Save
2. Atur Menu HOME.
   • Klik Menu ANTRIAN
   • Edit
   • Upload gambar untuk menu
   • save
   • Lakukan hal yang sama pada Menu PENJUALAN, RIWAYAT, DAFTAR PRODUK, DATA MEKANIK, DAFTAR JASA, LAPORAN, DATA PELANGGAN, INPUT PENGELUARAN, INFO TOKO
   • Save

BAGIAN 4: AUTOMASI (BOTS & ACTIONS)

1. Action: Buka Menu (Untuk Home)
   • Behavior->New Action
   • Name: Buka Menu
   • For a record of this table: MenuApp
   • Do this: App: go to another view within this app
   • Target:
     Copy Code

     LINKTOVIEW([TargetView])

   • Prominence: Hide
   • UX->MENU-> Behavior-> Event Actions_> Row Selected: Buka Menu
2. Action: Buka Form Stok (Otomatisasi Input Produk)
   • Behavior->New Action
   • Name: Buka Form Stok
   • For a record of this table: Produk
   • Do this: App: go to another view within this app
   • Target:
     Copy Code

     LINKTOFORM("StokMutasi_Form",
     "ID_Produk", [_THISROW].[ID_Produk], "Tipe",
     "Masuk")

   • Prominence: Primary
   • Behavior -> Only if this condition is true :
     Copy Code

     IN(ANY(SELECT(Pengguna[Role],
     AND([Username]=UserSettings("Username"),
     [Password]=UserSettings("Password")))), {"Admin"})

   • Setting Untuk Input Stok: Masuk ke View Produk_Form -> Behavior-> Event Actions -> Form Saved : Buka Form Stok
3. Action: Tambah Jualan (Shortcut Menu Penjualan)
   • Name: Tambah Jualan
   • Table: Transaksi
   • Do this: App: go to another view within this app
   • Target:
     Copy Code

     LINKTOFORM("INPUT
     JUALAN", "JenisOrder", "Penjualan")

   • Prominence: Display overlay
   • Behavior Condition:
     Copy Code

     CONTEXT("View") = "PENJUALAN"

4. Action: Cetak Struk (WEB PRINT)
   • Name: Cetak Struk
   • Table: Transaksi
   • Do this: External: Go to a website
   • Target:
     Copy Code

     CONCATENATE(
       "(https://xwijaya.com/p/nota-bengkel.html?",
       "toko=",
     ENCODEURL(ANY(InfoToko[NamaToko])),
       "&alamat=", ENCODEURL(ANY(InfoToko[Alamat])),
       "&telp=",
     ENCODEURL(ANY(InfoToko[telp])),
       "&id=",
     ENCODEURL([ID_Transaksi]),
       "&tgl=",
     ENCODEURL(TEXT([Tanggal], "dd/mm/yyyy HH:mm")),
       "&kasir=",
     ENCODEURL([User_Shift]),
       "&pelanggan=",
     ENCODEURL([ID_Pelanggan].[Nama]),
       "&plat=",
     ENCODEURL([ID_Pelanggan].[PlatNomor]),
       "&mekanik=",
     ENCODEURL([ID_Mekanik].[Nama]),
       "&total=", [TotalBelanja],
       "&bayar=", [NominalBayar],
       "&kembali=", [Kembalian],
       "&footer=",
     ENCODEURL(ANY(InfoToko[FooterStruk])),
       "&list_nama=",
         ENCODEURL(
           SUBSTITUTE(
             SELECT([Related
     TransaksiDetails][NamaItem_Cetak], TRUE),
             " , ", "||"
           )
         ),
       "&list_qty=",
         ENCODEURL(
           SUBSTITUTE(
             SELECT([Related TransaksiDetails][Qty],
     TRUE),
             " , ", "||"
           )
         ),
       "&list_harga=",
         ENCODEURL(
           SUBSTITUTE(
             SELECT([Related
     TransaksiDetails][HargaSaat], TRUE),
             " , ", "||"
           )
         ),
       "&list_sub=",
         ENCODEURL(
           SUBSTITUTE(
             SELECT([Related
     TransaksiDetails][Subtotal], TRUE),
             " , ", "||"
           )
         )
     )

   • Position: Primary
   • Behavior Only if this condition is true:
     Copy Code

     OR([Status]="Selesai", [Status]="Penjualan")

BAGIAN 5: Terakhir

1. UX ->Options ->Starting view : User Settings
2. Data ->Table->InfoToko-> Are updates allowed?: Updates Only
3. Data ->Table->MenuApp-> Are updates allowed?: Read Only