This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

Select Customer Name, then list transaction codes, user selects transaction code, Google script trig

Posted 2 weeks ago
HLvv
Bronze 1
Bronze 1
Reply posted on --/--/---- --:-- AM

Spec for AI to build RTS Slip

Just can't get it working??

Note: I am using the improved version of AppSheets

 

Samples of data sources:

 

RTSclean



ID

transaction

comment

warehouse

product

stock.long description

stock.bin

quantity

cost

date

type

value

Supplier Name

ID-1

10037

S006

01

13133

150mm GALV M.I ELBOW 90 F/F MECH90

G/0/36

-10

670

Sat Jul 05 2025 09:00:00 GMT+0200 (South Africa Standard Time)

RTS

-6700

STEWARTS & LLOYDS HOLDINGS PTY

ID-2

10037

S006

01

10239

150mm GALV M.I EQUAL TEE Q130

G/0/32

-3

825

Sat Jul 05 2025 09:00:00 GMT+0200 (South Africa Standard Time)

RTS

-2475

STEWARTS & LLOYDS HOLDINGS PTY



Select RTS

 

ID

RTS

Date

Time

Supplier code

Supplier name

Slip ID

RTS List

30679c8f

 

6/6/2025

15:14:40

 

SUNRIDGE IMPORTERS & WHOLESALERS

 

10039 , 10041 , 10042 , 10044

dc4a077a

 

6/6/2025

15:19:20

 

SUNRIDGE IMPORTERS & WHOLESALERS

 

10039 , 10041 , 10042 , 10044



Required Views:

 

  • select RTS by supplier

The first view is a form, display “select RTS by supplier”. 

The input field is to select the supplier name from a drop down list. The list is found in [RTSclean], [Supplier Name]. The list must be unique values only (unique eliminate duplicates). The user selects only one. The selection is recorded in  Table: Select RTS, [Supplier name]. On save the next view is displayed ‘Select the supplier transaction code’.



  • Select the supplier transaction code

Text field to display the selected supplier.

Input field to display the list of transaction codes for the selected supplier.

The list must be calculated: Use the supplier name selected, find all unique transaction codes in RTSclean, transaction.



Write a detailed step by step instruction to build this solution using AppSheet. Make the instructions simple to follow. 



Phase 1: Initial App Setup & Data Source Integration

  1. Create Your AppSheet Application:

    • Go to the AppSheet platform.
    • Start a new application, choosing to begin from your existing data source (e.g., Google Sheet or Excel file) that contains your RTSclean and Select RTS tables.
  2. Add Tables to AppSheet:

    • In the AppSheet editor, navigate to Data > Tables.
    • Click + Add New Table.
    • Select your data source and add both the RTSclean and Select RTS tables.
    • Configure Table Permissions:
      • For the RTSclean table: Click its name, go to Updates, and set permissions to Read-Only. This prevents users from altering your source data.  
      •  
      • For the Select RTS table: Click its name, go to Updates, and set permissions to Adds, Deletes, Updates. This allows the app to create and modify records.  
      •  
  3. Configure Select RTS Table Columns:

    • Navigate to Data > Columns and select the Select RTS table.
    • ID Column:
      • Set Type to Text.
      • Mark the Key checkbox as Yes.  
      •  
      • Set Initial Value to the expression: UNIQUEID().  
      •  
      • Set Show? to No (for final user experience) and Editable? to No.  
      •  
    • Supplier name Column:
      • Set Type to Text.  
      •  
      • In the Valid If property, enter the expression: SORT(UNIQUE(SELECT(RTSclean, ISNOTBLANK()))).  
      •  
      • Set Auto-complete to On.  
      •  
      • Ensure Show? is Yes and Editable? is Yes.  
      •  
    • Selected Transaction Code Column:
      • Set Type to Enum (for single selection) or EnumList (for multiple selections).  
      •  
      • In the Valid If property, enter the expression: SORT(UNIQUE(SELECT(RTSclean[transaction], =.))).  
      •  
      • Set Auto-complete to On.  
      •  
      • Ensure Show? is Yes and Editable? is Yes.  
      •  

Phase 2: Designing "Select RTS by Supplier" View (The First Form)

  1. Create the Form View:
    • Navigate to UX > Views.
    • Click + New View.
    • View Name: Select RTS by Supplier.  
    •  
    • View Type: Form.  
    •  
    • For Table: Select RTS.  
    •  
    • View Position: Set to Ref.
    • Columns to Show in Form: Under the Columns section, explicitly select Supplier name.

Phase 3: Implementing Navigation and Context Transfer

  1. Create the Navigation Action:

    • Navigate to Behavior > Actions.
    • Click + New Action.
    • Action Name: Go to Select Transaction Code Form.
    • For a record of this table: Select RTS.
    • Do this: App: go to another view within this app.
    • Target: Enter the expression: LINKTOFORM("Select Transaction Code", "ID",.). This ensures the second form opens the same record.  
    •  
  2. Attach Action to Form Save:

    • Navigate back to UX > Views.
    • Select your Select RTS by Supplier view.
    • Scroll to the Behavior section.
    • Under the Form Saved property, select the action you just created: Go to Select Transaction Code Form.

Phase 4: Designing "Select Transaction Code" View (The Second Form)

  1. Create the Form View:
    • Navigate to UX > Views.
    • Click + New View.
    • View Name: Select Transaction Code.  
    •  
    • View Type: Form.  
    •  
    • For Table: Select RTS.  
    •  
    • View Position: Set to Ref.
    • Columns to Show in Form:
      • Supplier name (This will display the selected supplier from the previous form).
      • Selected Transaction Code (This is where the user will select the transaction).

Phase 5: Testing and Validation

  1. Test Case 1: Initial Form Display

    • Action: Open the AppSheet application.
    • Expected Outcome: The "Select RTS by Supplier" form loads, displaying the Supplier name dropdown populated with unique, sorted supplier names.
  2. Test Case 2: Supplier Selection and Navigation

    • Action: Select a supplier from the Supplier name dropdown and click "Save."
    • Expected Outcome: The app seamlessly navigates to the "Select Transaction Code" form without any blank screens.
  3. Test Case 3: Context Preservation

    • Action: On the "Select Transaction Code" form, observe the Supplier name field.
    • Expected Outcome: It correctly displays the supplier name selected in the previous form.
  4. Test Case 4: Dynamic Transaction Dropdown

    • Action: Click on the Selected Transaction Code input field.
    • Expected Outcome: The dropdown displays only transaction codes associated with the supplier selected in the first step.
  5. Test Case 5: Saving the Second Form

    • Action: Select a transaction code and click "Save."
    • Expected Outcome: A new row is added to your Select RTS data source, containing both the Supplier name and Selected Transaction Code in the same record.

 

1 1 57
Topic Labels
1 REPLY 1

Posted on --/--/---- --:-- AM
PauloExpert
Silver 2
Silver 2
Reply posted on --/--/---- --:-- AM

Thanks for the detailed brief and sample data! Based on your goal and what you've built so far, here's a step-by-step guide to implement the two-step form system in AppSheet using your RTSclean and Select RTS tables.

 

 

 

✅ PHASE 1: Initial App & Data Setup

 

1. Create App & Connect Data

 

Go to AppSheet

 

Start a new app and connect the Google Sheet or Excel file containing:

 

RTSclean

 

Select RTS

 

 

 

✅ PHASE 2: Table Configuration

 

2. RTSclean Table Settings

 

Set as Read-Only

 

Key Column: ID (or another unique value)

 

 

3. Select RTS Table Settings

 

Set as Adds/Updates/Deletes

 

Key Column: ID

 

Type: Text

 

Initial value: UNIQUEID()

 

Show?: OFF

 

 

 

4. Configure Columns in Select RTS

 

➤ Supplier name

 

Type: Enum

 

Valid If:

 

SORT(UNIQUE(SELECT(RTSclean[Supplier Name], ISNOTBLANK([Supplier Name]))))

 

Auto-complete: ON

 

 

➤ Selected Transaction Code (new column you create)

 

Name: Selected Transaction Code

 

Type: Enum

 

Valid If:

 

SORT(UNIQUE(

  SELECT(RTSclean[transaction], [Supplier Name] = [_THISROW].[Supplier name])

))

 

Auto-complete: ON

 

 

 

✅ PHASE 3: View 1 – "Select RTS by Supplier"

 

5. Create View

 

Go to UX > Views

 

View Name: Select RTS by Supplier

 

For Table: Select RTS

 

View Type: Form

 

Only show Supplier name in this form

 

 

6. Create Navigation Action

 

Go to Behavior > Actions

 

New Action:

 

Name: Go to Select Transaction Code

 

For a record of this table: Select RTS

 

Do this: App: go to another view within this app

 

Target:

 

LINKTOROW([_THISROW].[ID], "Select Transaction Code")

 

 

 

7. Attach Action to Form Save

 

Go to UX > Views > Select RTS by Supplier

 

Scroll to Behavior > Form Saved

 

Choose Go to Select Transaction Code

 

 

 

✅ PHASE 4: View 2 – "Select Transaction Code"

 

8. Create View

 

View Name: Select Transaction Code

 

For Table: Select RTS

 

View Type: Form

 

Show Columns:

 

Supplier name (readonly if desired)

 

Selected Transaction Code

 

 

 

✅ PHASE 5: Test Everything

 

✅ Test 1: Supplier Dropdown

 

Should show unique supplier names from RTSclean.

 

 

✅ Test 2: Navigation

 

After selecting and saving supplier, user is taken to second form.

 

 

✅ Test 3: Transaction Code Filtering

 

Should show only the transactions for the selected supplier.

 

 

✅ Test 4: Data Saved

 

One row in Select RTS should include both:

 

Supplier name

 

Selected Transaction Code

 

 

 

0 Likes
Top Labels in this Space