Aplikasi Kasir Bengkel & Sparepart (AppSheet)

BAGIAN 1: DATABASE (GOOGLE SHEETS)

Buat file Google Sheet

Sheet: Mekanik
- ID_Mekanik
- Nama
- Kontak
- TipeKomisi
- NilaiKomisi
- Status
Sheet: Produk
- ID_Produk
- NamaProduk
- Satuan
- HargaBeli
- HargaJual
- TypeKendaraan
- MerekKendaraan
- Gambar
Sheet: Jasa
- ID_Jasa
- NamaJasa
- HargaJasa
Sheet: Pelanggan
- ID_Pelanggan
- Nama
- Telepon
- Alamat
- TypeKendaraan
- MerekKendaraan
- PlatNomor
Sheet: Transaksi
- ID_Transaksi
- Tanggal
- ID_Pelanggan
- JenisOrder
- ID_Mekanik
- TotalBelanja
- TotalJasa
- TotalHPP
- NominalBayar
- Kembalian
- Status
- Catatan
- User_Shift
Sheet: TransaksiDetail
- ID_Detail
- ID_Transaksi
- ItemType
- Produk
- Jasa
- Qty
- HargaSaat
- HPPSaat
- Subtotal
Sheet: StokMutasi
- ID_Mutasi
- Tanggal
- ID_Produk
- Tipe
- Qty
- Keterangan
- ID_Referensi
Sheet: Pengeluaran
- ID_Pengeluaran
- Tanggal
- Kategori
- Nominal
- Keterangan
Sheet: Pengguna
- Username
- Password
- Role
Sheet: InfoToko
- ID
- NamaToko
- Alamat
- Telp
- FooterStruk
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.

Column 1:
- Name: Username
- Type: Enum
- Values: ADMIN, KASIR
- Show: ON
- Data Validity -> Require: ON
- Update Behavior -> Editable? : ON
- Display -> Label: ON
Column 2:
- Name: Password
- Type: Text
- Show: ON
- Data Validity -> Valid if :
```
LOOKUP(USERSETTINGS("Username"),"PENGGUNA","Role","Password")
= [Password]
```
- Invalid value error : pasword salah!
- Require: ON
- Update Behavior -> Editable? : ON
Simpan.

B. Tabel: Pengguna

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

C. Tabel: Mekanik

ID_Mekanik: Type Text, Key (Centang), Label (UnCentang), Initial Value:
```
UNIQUEID()
```
Nama: Type Name, Label (Centang).
Kontak: Type Phone.
TipeKomisi: Type Enum (Values: Persen, Nominal).
NilaiKomisi: Type Decimal
Status: Type Enum (Values: Aktif, Nonaktif).

D. Tabel: Produk

ID_Produk: Type Text, Key (Centang), Label (UnCentang), Initial Value:
```
UNIQUEID()
```
NamaProduk: Type Text, Label (Centang).
Satuan: Type Enum (Pcs, Botol, Set, Lembar,Liter,Kilo).
HargaBeli: Type Price Currency: Rp
HargaJual: Type Price Currency: Rp.
TypeKendaraan: Type Enum (Values: Mobil, Motor, Umum).

MerekKendaraan: Type Enum (Allow Other Values: ON).

Valid_If:

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")

)

Gambar: Type Image.

Virtual Column: Stok Awal.

App Formula:

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.

Virtual Column: Sisa Stok.
- App Formula:
```
[Stok Awal] - SUM([Related
TransaksiDetails][Qty])
```
- Type: Number.

E. Tabel: Jasa

ID_Jasa: Type Text, Key (Centang), Label (UnCentang), Initial Value:
```
UNIQUEID()
```
NamaJasa: Type Text, Label (Centang).
HargaJasa: Type Price Currency: Rp.

F. Tabel: Pelanggan

ID_Pelanggan: Type Text, Key (Centang), Label (UnCentang), Initial Value:
```
UNIQUEID()
```
Nama: Type Name, Require? (Centang). Initial Value: "Pelanggan".
Telepon: Type Phone.
Alamat: Type LongText.
TypeKendaraan: Type Enum (Values: Mobil, Motor).

MerekKendaraan: Type Enum (Allow Other Values: ON).

Valid_If:

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")

)

PlatNomor: Type Text, Require? (Centang).

G. Tabel: Transaksi

ID_Transaksi: Type Text, Key (Centang), Initial Value:
```
UNIQUEID()
```
Tanggal: Type DateTime, Initial Value:
```
NOW()
```
ID_Pelanggan: Type Ref (Source Table: Pelanggan).
- Show?:
```
[JenisOrder]="Servis"
```
- Require?:
```
[JenisOrder]="Servis"
```
- Initial Value:
```
IF([JenisOrder]="Penjualan", "UMUM",
"")
```
JenisOrder: Type Enum (Values: Servis, Penjualan), Initial Value: "Servis".
ID_Mekanik: Type Ref (Source Table: Mekanik).
- Show?:
```
[JenisOrder]="Servis"
```
- Require?:
```
[JenisOrder]="Servis"
```
TotalBelanja: Type Price. Currency: Rp.
- App Formula:
```
SUM([Related
TransaksiDetails][Subtotal])
```

TotalJasa: Type Price. Currency: Rp.

App Formula:

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

TotalHPP: Type Price. Currency: Rp.
- App Formula:
```
SUM([Related
TransaksiDetails][TotalModal])
```
NominalBayar: Type Price. Currency: Rp.
- Show If:
```
OR([Status]="Selesai",
[Status]="Penjualan")
```

Kembalian: Type Price. Currency: Rp.

Show If:

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

App Formula:

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

Status: Type Enum (Values: Proses, Selesai, Penjualan, Batal).
- Initial Value:
```
IF([JenisOrder]="Penjualan", "Penjualan",
"Proses")
```
- Update Behavior -> Editable?:
```
AND([Status] <> "Selesai", [Status] <>
"Penjualan")
```
User_Shift: Type Text, App Formula :
```
UserSettings("Username")
```
, Editable?: OFF.
Virtual Column: KeuntunganBersih.
- App Formula:
```
[TotalBelanja] - [TotalHPP]
```
- Type: Price.

Virtual Column: KomisiMekanikHitung.

Type: Price

.App Formula:

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

Virtual Column: hari.
- Type: date
- .App Formula:
```
DATE([Tanggal])
```
Virtual Column: bulan.
- Type: Text
- .App Formula:
```
TEXT([Tanggal], "MM-YY")
```
Virtual Column: tahun.
- Type: Number
- .App Formula:
```
YEAR([Tanggal])
```

H. Tabel: TransaksiDetail

ID_Detail: Type Text, Key (Centang), Initial Value:
```
UNIQUEID()
```
ID_Transaksi: Type Ref (Source Table: Transaksi), Is a part of?: ON.

ItemType: Type Enum (Values: Produk, Jasa).

Initial Value:

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

Valid_If:

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

Editable?:

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

Produk : Type Ref (Source Table: Produk).
- Show? :
```
[ItemType] = "Produk"
```
Jasa: Type Ref (Source Table: Jasa).
- Show? :
```
[ItemType] = "Jasa"
```
Qty: Type Number, Initial Value: 1.
- Valid_If:
```
IF([ItemType]="Produk", [Qty] <= [Produk].[sisa
stok], TRUE)
```
- Error Msg: "Stok tidak cukup!"

HargaSaat: Type Price.

Initial Value:

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

HPPSaat: Type Price.

Initial Value:

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

Subtotal: Type Price, App Formula:
```
[Qty] * [HargaSaat]
```
.
Virtual Column: TotalModal.
- App Formula:
```
[Qty] * [HPPSaat]
```
- Type: Price.

Virtual Column: NamaItem_Cetak (Kolom Baru untuk Web Print).

App Formula:

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

Type: Text

I. Tabel: StokMutasi

ID_Mutasi: Type Text, Key (Centang), Initial Value:
```
UNIQUEID()
```
Tanggal: Type DateTime, Initial Value:
```
NOW()
```
ID_Produk: Type Ref (Source Table: Produk).
Tipe: Type Enum (Values: Masuk, Keluar).
Qty: Type Number.
Keterangan: Type Text.
ID_Referensi: Type Text, Editable?: OFF.

J. Tabel: MenuApp

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

K. Tabel: InfoToko

ID: Type Number, Key (Centang).
NamaToko: Type Text.
Alamat: Type Text.
Telp : Type Text.
FooterStruk: Type Text.

L. Tabel: Pengeluaran

ID_Pengeluaran: Type Text, Key (Centang), Initial Value:
```
UNIQUEID()
```
Tanggal: Type DateTime, Initial Value:
```
NOW()
```
Kategori: Type Enum (Values: Listrik, Air, Gaji, Lainnya).
Nominal: Type Price.
Keterangan: Type Text.

BAGIAN 3: UX (TAMPILAN)

A. Buat SLICE (Data -> Slices)

Slice_Antrian:
- Source Table: Transaksi
- Row Filter Condition:
```
AND([Status]="Proses",
[JenisOrder]="Servis")
```
Slice_Penjualan:
- Source Table: Transaksi
- Row Filter Condition:
```
AND([Status]="Penjualan",
[JenisOrder]="Penjualan")
```
Slice_Riwayat:
- Source Table: Transaksi
- Row Filter Condition:
```
OR([Status]="Selesai",
[Status]="Penjualan")
```

B. Buat VIEW (UX -> Views)

Isi database menuapp dengan

ID_Menu	TargetView	Gambar
1	ANTRIAN
2	PENJUALAN
3	RIWAYAT
4	DAFTAR PRODUK
5	DATA MEKANIK
6	DAFTAR JASA
7	LAPORAN
8	DATA PELANGGAN
9	INPUT PENGELUARAN
10	INFO TOKO

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

View ANTRIAN:
- Data -> Slice ->create new slice
- Slice Name : Slice_Antrian
- Source Table : Transaksi
- Row filter condition :
```
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
View PENJUALAN:
- Data -> Slice ->create new slice
- Slice Name : Slice_Penjualan
- Source Table : Transaksi
- Row filter condition :
```
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
View RIWAYAT:
- Data -> Slice ->create new slice
- Slice Name : Slice_Riwayat
- Source Table : Transaksi
- Row filter condition :
```
[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
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
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
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
View INPUT PENGELUARAN
- View name:INPUT PENGELUARAN
- For this data: Pengeluaran
- View type: Table
- Position: Ref
- Sort by : Tangal dan Desc
- Save
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
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
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)

Buat View HOME.
- View name:HOME
- For this data: MenuApp
- View type: Gallery
- Position: Next
- Save
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)

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:
```
LINKTOVIEW([TargetView])
```
- Prominence: Hide
- UX->MENU-> Behavior-> Event Actions_> Row Selected: Buka Menu
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:
```
LINKTOFORM("StokMutasi_Form",
"ID_Produk", [_THISROW].[ID_Produk], "Tipe",
"Masuk")
```
- Prominence: Primary
- Behavior -> Only if this condition is true :
```
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
Action: Tambah Jualan (Shortcut Menu Penjualan)
- Name: Tambah Jualan
- Table: Transaksi
- Do this: App: go to another view within this app
- Target:
```
LINKTOFORM("INPUT
JUALAN", "JenisOrder", "Penjualan")
```
- Prominence: Display overlay
- Behavior Condition:
```
CONTEXT("View") = "PENJUALAN"
```

Action: Cetak Struk (WEB PRINT)

Name: Cetak Struk
Table: Transaksi
Do this: External: Go to a website

Target:

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:
```
OR([Status]="Selesai", [Status]="Penjualan")
```

BAGIAN 5: Terakhir

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

Untuk lebih jelas silahkan tonton video berikut, jangan lupa subscribe EKISFAMILY

Panduan Pembuatan Kasir Bengkel Menggunakan Appsheet

Aplikasi Kasir Bengkel & Sparepart (AppSheet)

BAGIAN 1: DATABASE (GOOGLE SHEETS)

BAGIAN 2: PENGATURAN DATA (APPSHEET)

A. Pengaturan Login (USER SETTINGS)

B. Tabel: Pengguna

C. Tabel: Mekanik

D. Tabel: Produk

E. Tabel: Jasa

F. Tabel: Pelanggan

G. Tabel: Transaksi

H. Tabel: TransaksiDetail

I. Tabel: StokMutasi

J. Tabel: MenuApp

K. Tabel: InfoToko

L. Tabel: Pengeluaran

BAGIAN 3: UX (TAMPILAN)

A. Buat SLICE (Data -> Slices)

B. Buat VIEW (UX -> Views)

C. Menu Utama (HOME DASHBOARD)

BAGIAN 4: AUTOMASI (BOTS & ACTIONS)

BAGIAN 5: Terakhir