I want to make a form to key in disposal value and then the data will flow back to my main google sheet table.
Google Sheet
Attach sample file for your testing. There are 2 sheets which use to make the app: Order History, App Use
- Order History:
- A master sheet where I keep track of my purchase or disposal quantity, with detail information.
- Due to I want my app only show 21 days worth of item, and group up similar line for each branch. Hence, I archive with “App Use” sheet by using query.
Image1
2. Appsheet:
-
This sheet query is to filter only rows within 21 days and group by col2 to col7 of the “Order History” sheet.
=QUERY(‘Order History’!A:K, “SELECT Col2, Col3, Col4, Col5, Col6, Col7, SUM(Col11) WHERE Col1 >= DATE ‘” & TEXT(TODAY()-21, “yyyy-mm-dd”) & “‘ GROUP BY Col2, Col3, Col4, Col5, Col6, Col7 Label Sum(Col11) ‘Quantity'”)
-
With the above formula, I will get the unique items for the specific branch.
Image2
Google Appsheet
In Appsheet, I have 3 table. 2 from google sheet (stated on the above) and 1 from Google Drive folder consisting of Flower Pictures.
Folower Pictures Table:
-
I managed to use the virtual column formula to bring Flower Pictures into both tables with the following formula. As you can see the flower picture name is by “Description” and “Colour”
Concatenate(“https://drive.google.com/thumbnail?sz=w1000&id=”,Any(Select(Flower Pictures[_ID],[File]=CONCATENATE([THISROW].[Description],”“,[_THISROW].[Colour],”.png”))))
OPTION 1
My aim is that before deriving the form, I want to be able to at:
- Screen1: select Branch
- Screen2: select Flower Pictures of the specific branch
- Screen3: the form will pre-populated the Branch, No, Description
selection from previous steps.
For example referring to Image2,
- Screen1: JEM
- Screen2: Flower Pictures
- Screen3: Pre-populated Branch: Jem, Description: HH, No: 2000506000004 , Color: Light Pink. What user need to do is just write “Quantity”. Then click submit where new line will be created back to google sheet.
OPTION 2
Create a view which is a form:
Screen1: allow user to scan bar code. Then other field will be pre-populated base on the bar code. What user need to do is just write “Quantity”. Then click submit where new line will be created back to google sheet.