วีดีโอ

You are welcome!

Thank you! Cheers!

Sure. I'm just outdoors right now. I'll give you some pointers when I get to a steady place. Thanks for the sub. Cheers!

Thanks.

Glad it was helpful!

Many thanks!

You give me too much credit; but, Thanks a lot! ☺️

Unfortunately not. I've requested this feature many years ago. But as of today, INPUT() still doesn't work with that action. The row gets added without the popup ever showing up. The only available alternative is the LINKTOFORM() function, which is not a popup, but can give you controls as to what will be the data entered in each field based on the current record.

It is, right? 😄

Welcome

Hi Lionel, Thanks for sharing. Using the User Settings as a custom login page, aling with a Users table with their usernames and passwords is a common workaround thst I also suggest to most clients with similar situations as yours. I have yet to make a tutorial for it but really, it's very simple but effective. To your other question, I just make sure I always consider myself as still a beginner, so that I will always be thristy for more ways on how to implement things in AppSheet. I still attend Google AppSheet Officehours even if sometimes, they always still teach the basics. But the best source of new information are actually the clients who I get to consult with, with unique use cases and challenges. While I try to assist them on implementing requirements of their apps, I get to learn new things and tricks in order to come up with a solution with their specific challenges. I guess, experience with actual projects is still the best source of knowledge with AppSheets.

Hi Lionel, I really appreciate your praises. Thank you very much. I am from the Philippines 🇵🇭 . Pinoy is a local term we use for Filipino. My background came mostly from Ms Access programming and Javascript Frameworks. Before AppSheet I used to develop responsive web apps for clients via Google Apps Script code and its HTML Service., and Google Sheets as the backend. This was because most of them utilize Google Workspace. It was really time-consuming just to be able to build just one view. So when AppSheet came, it was almost a no brainer for me, even if AppSheet still used to be clunky and there were still implementations that required JSON notation. When Google acquired AppSheet, oh man, I can't tell you how happy I was that building a functioning app was just like designing spreadsheet templates.

Did it help? 😁

Did you try to put "" in the default value of the Input? This way, it forces the user to make a selection since the field in the popup is blank.

​@@FullOfSheets Thanks so much for your reaply. I tried but still the value comes from the first row. but I want the value in the input to be coming from the first row. Let me explain again. My understanding about the INPUT function is as per below. 1. Presse the action buton and the popup appears after sellecting rows with the appsheet built-in multi-selection function. The default value in the input comes from the first row. 2. Change the value in the input on the popup and it will change the value in each row only on the frontend***. 3. If you press the save button, the changes will be written onto the actual DB(googlesheet,etc) if you press the cancel button, the frontend will be back to what the DB says. the issue comes from the number 2. if you don't change the value in the input and just presse save, anything won't happen even if the value in the second row is not the same as the value in the input. if there is only one input, I still can use the batch update by changing the value from one to another and back to what it was but I am faced with the worse issue if there are multiple inputs. those inputs work independently so the values on the frontend change only based on the releated inputs. so I have to change the values in all the inputs from one to another and back to what they originally were even if there are some inputs that I don't want to change and colums in some rows are different form the first row. sorry for my bad explanantion and I hope you understand.

so the possible solution would be the one to match all the values based on the first row on the frontend as soon as you presse the action button and the popup appears. I think , in that way, all the changes will be detected and written into DB.

@stevekim1524 I understood what you meant. But I'm not sure you understand what the Input is supposed to do. The Input function does a batch change to all your selected rows. This means that whatever you choose in the Input pop up fields will apply to all the rows you selected. It is like going to the Google Sheet and copy pasting the values to the columns if all the rows you want - and it will all be the same values, otherwise you will have to do multiple copy-pastes separately. If you have multiple fields in your Input popup, then it will change all the values of all those fields/columns for all the rows you selected. Otherwise, you will have to do multiple separate inputs, as how you do multiple copy-pastes in the sheet. You can not use it to apply the changes only on some of the rows you selected, or only on some of the columns.

@@FullOfSheets I don't think I explained enough to make you understand what I meant. Of course, I want to make the value in the column in all the selected rows the same. why would I use the batch update if I didn't? simply put, let's say you selected two rows. the value in the first row is "A", and the value in the second one is "B". when you push the action button for input, the default value is "A" and I would like to make the values in both rows "A", meaning you don't have to change the value of the input, right? and all I think you have to do is you press save button thinking that the value in both rows will be changed to "A". But the value in the second won't be changed to "A" because you didn't change the value of the input at all(changing the value of the input is a trigger). you just used the initial value. I assuem if you don't change the value of the input, Appsheet won't change the value in the second row. Technically, Appsheet won't do anything if you just click save button with the initial value.

Yes available sya sa core plan Are you referring to the flask icons for shw, editable, and require tickboxes in the table editor? Click mo ung profile icon mo sa top right, then editor settings.

@@FullOfSheets Yes, para hindi mo na need pumunta sa pen button, pero sakin kase hindi ko makita yug option na katulad sayo na pinakita mo dito sa video.

Talaga? That's weird ha. Kasinkahit sa free plan na gsmit ko, meron nun e.

@@FullOfSheets Yes Sir.. medyo weird nga po.. kahit sa free ko din po wala.. hehehe. anyway, Thanks po! Nice video tutorial, napaka clear.

@@FullOfSheets I got it na po Sir, dapat po pala nasa improved ecitor. im using most of the time kase na legacy. Thanks thanks!

What did you mean by that? What was the current view before you clicked the button to trooger the pop-up?

@@FullOfSheets For example, I am in view "X", I make the Input for view "X", but when I press "save" after saving the value in the "Input", view "Y" opens

I don't experience it so it must be something else in your app, specifically. Check first the Finish View of the original form of "x". That may be teiggering to change the view after editing. Take note that making the change through Input() is like opening the form, making tue edit, and then saving it.

The last method is the text + suggested values. What did you mean by combining two columns?

I'm glad it helped.

Did you mean add new rows on another table, based on values from the vurrent table?

​@@FullOfSheetswe are allowed to add new row on another table if only we update the certain value of a column on current table. Otherwise, we can't add new data to that related table

@@konsultannumerology838 and is the other table a child table of the current one? How are you adding a record to it, assuming the condition is met?

@@FullOfSheets i'm not sure about child table, but 2nd table related to the 1st one. it's like remove the add action button on 2nd table, until the condition is met (which is updated value in a certain column on table 1)

@konsultannumerology838 yes. It's called a child table. This happens when the child table has a Ref column poit ing to the parent table, and the parent table automatically creates a [Related xyz] virtual column. What happens is that in the Detail View of the Parent table, you will see an inline view table with the "View" and "Add" buttons on the bottom right. Is this what you're referring to?

You probably haven't encountered a project that would would make you think of this. Let's say, for example, i have an Orders Table with a chuld record of Order Items where I add a Product for each item (like a sales invoice). I can just simply click "Add" in the child table, then select a product. It then creates 1 entry in the child table for that product as an item in the order. But what if I need to add 20 items in the order? Then that's about clicking the "Add" button 20 times, clicking the product dropdown and scrolling thru the product, selecting the prooduct, then climing save. You have to do it 20 times. So maybe just add an Enum list to the Order to select the products you want for the order, then an Order Items record will be auto created in the child table automatically.

You can use the ORDERBY() formula in the Valid-If column setting

Simply have a #Montly Count# table with the information you need to be recorded. Think of it this way, if pretending that everything works on paper, how would your monthly count form print out look like? What are the information you will wirte on the paper? Perhaps you wll have the following, right? [Month] [Date of Count] [Counted By] INVENTORY ITEMS [Item Name] [Qty] Once you have this figured out, then simlply implement the same in your app. In the example above, you may need three tables. #Monthly Count# #Related Monthly Inventory Items# #Inventory Items# You may refer to my video about Many-to-Many relationships as a guide in setting up the table relationships.

I'm a little confused since you mentioned "table". Looping can only be do e from a record/row of that table. We cannot loop using the entire table.

Thanks for letting me know. I'll try to do something about it. This is the knly video where I uploaded thenvideo without music then only added the music later onnusing youtube studio. I'll check if there's a way I can lower down then volume.

Can you check if it's better now?

In my Best Practices video, I mentioned "Data Flow First, UI Design Later". Which means that before you even login to AppSheet, you should have structured and designed how your data will appear in the Sheet or Sheets. If, say, I would ask you to prepare a spreadsheet report of all the inventory you manage from all the different stores, and I want you to send it to me in a single sheet, how will that look like? You will probably send me a very long list with a lot of columns that has the store information, product information, the count of each product, and the whole she-bang, rigth? That, for me, is a very good starting point, as it forces you to come up with a very flat and normalized data. Using that huge sheet would allow you to create pivot tables where you can group prosuct information per store, per category, per whatever.

Once you have this single sheet of data, the next thing you want to do is to further "normalize" the data. In this long list of columns, youbcan easily spot information that belongs to the same group. Maybe, color code them, to make it visual? Once you've done that, it would be very easy for you to now identify what we call the "entities" of your database - the major building blocks of your data. For example, you have stores, products, employees, purchases, orders, customers, etc.

Once you have identified this, then you can copy-paste the information for each of the entities in their separate tables. In this case, I imagine you having a Stores table. There will also be a Products table with a column called [Store], and this is either a Ref or an Enum-Ref column pointing to the Stores table. This would then create a Virtual column in your Stores table called [Related Products]. Now your Employees table would also have a similar [Store] column identifying each employee to which store they are assigned to, as well as roles. You can then use these columns to limit visibility to store inventory information. This is exactly what I will demonstrate in an upcoming series of "Building Inventory Management App from scratch". But I may not be able to start it until I finish the current series (2 more videos) away. I'm having difficulties finding time to post the videos due to a large influx of Consultation calls and App development projects in my queue. So please stay tuned. 🙏

Glad it was helpful!

@@FullOfSheets I have been modifying a template app and have noticed that changing column or table names causes problems as the system doesn't seem to propagate the changes throughout expressions etc and perhaps seems to just delete some things that now break the system. What is the most efficient process to use to make name changes to columns and tables without losing functionality/messing things up?

Haha. That's just the way appsheet works. If you are simply changing column names, then you can just edit the column name in the app editor and in your sheet. But if you moved column orders, or added/deleted columns in the sheets then you will have to regenerate the table schema. Take note though, that if you used the old column names in one or more expressions across tables and views, then you will have to manuall update all of those to the new name. If you are changing the Sheet/Tab name, then you will have to let the app break first, so you can go back to the Table settings and select the new sheet name on the Storage section of the Table's settings.

@@FullOfSheets Thanks so much! I was kind of hoping there was some easier way. Thank-you for saving me from more searching

No problem, mate!

You're very welcome!

Thank you. I appreciate it a lot!

Indeed. But only works if there is a Ref or Enum-Ref column you can use to dereference, though.

Next, you cannot use a Ref nor an Enum-Ref, because as discussed in my "Top 5 Ways to Create dropdowns in Appsheet" video, a Ref and Enum-Ref column type will always expect and save the key column. So by using the Valid-If formulas to return the Category, SubCategory, or Specific Issue like in this video, Appsheet would throw you those Nasty yellow exclamation marks in your dropdown. In other words, you can not use dereferencing . Ok, so first things first, the Group and Location information should be available already in the same Dropdowns table where the Categories, Subcategories, and Specific Issue is located. Meaning, your dropdown tables will now have the following columns: Category Subcategory Specific Issue Group Location Next, use the same Valid if formulas demonstrated in the vudeo to create the dependencies. Then, on your Group and location columns, use LOOKUP() in the App Formula to get the Group and Location Values from the Dropdowns table. For the Group: LOOKUP([SPECIFIC ISSUE], "DROPDOWNS", "SPECIFIC ISSUE", "GROUP") For the Location: LOOKUP([SPECIFIC ISSUE], "DROPDOWNS", "SPECIFIC ISSUE", "LOCATION")

@@FullOfSheets Hi JP! Thank you for the detailed advice. I've followed your instructions. They seem to mostly work. The LOOKUP however always provides the value in the first row of the dropdown table. Would I need to modify the LOOKUP equation or use ANY(Select)? In case you or others may find it helpful, here's a link to a Google Drive with the tables I recreated per your video. drive.google.com/drive/folders/1XWvVMnkpoNwIQhxUaWdwjcZXjA7r-G5z?usp=sharing Thank you!

It shouldn't be if the [Specific Issue] unique for each row. You can try using Index(lookup(), 1) or maybe changing it to select. But then again, both assumes that the Specific Issue is unique across all rows in the dropdowns table. Thanks for sharing your tables. I'm sure it would help some sheety pipz in the community. But I think, try first using [_thisrow] on the match value. Lookup([_thisrow].[SPECIFIC ISSUE], "DROPDOWNS", "SPECIFIC ISSUE", "GROUP"). I suspect this should do it.

@@FullOfSheets Yes, that fixed it! Thank you for the advice, JP. Really appreciate it! 🙏🙏

Awesome! Good luck with your project, and Happy Building! 😊

You can't. Key columns should always be unique because it allow to identify sa specific column.

If you have duplicate appsheet starts to malfunction.

You're right! 💯

A workaround I can think of is to have another Related Tasks virtual column, but instead of using ref rows, you should use a FILTER() or SELECT() statements which allows you to set a criteria to return only those that are not yet completed. Make sure that this VC is a List type column, and in the column settings, must be a Ref element type pointing to the task list. You can then have this VC hidden and use this instead in your Index() function. INDEX( [EnumList] - [Hidden Related Tasks][Task Name], 1)

@@FullOfSheets Thank you, I really appreciate the help. My formula ended up being slightly different than what you suggested just in case anyone else ends up reading these comments. I made the virtual column but I filtered it by Completed tasks instead of non-completed tasks. Then I subtracted the virtual column of the completed task from the list of all of the tasks. This resulted all of the non-completed tasks and is what I ended up subtracting from the EnumList. The result has this loop automatically add the tasks from the workflow to the pending tasks list as long as the task is not currently pending, even if the tasks was completed in the past. index([EnumList]-(Tasks[All Task]-Tasks[Completed Tasks]),1)

Great Job! Thanks for sharing what you ended up with as well. Be careful and make sure you test this with another project record as well. Since you are directly referencing to the Tasks table instead of the Related Tasks, you might end up comparing to all the tasks that are pending from all the projects in your app, instead of only the comparing it to the Pending tasks related to this project only. But if it works, then it works. There is always more than 1 way to skin a cat. 😻

@@FullOfSheets The "Tasks[Completed Tasks] in the formula is a virtual column so it does appear to be working. At the end of your video you mentioned having the row automatically delete once the bot completes the loop. What is the best way to set that up? I have been trying a couple of different ways but can't seem to figure out how to have it delete the initial input once the loop completes.

You will have to create another action that deletes the row specifying the key in the action's behavior condition. Also ensure that the condition also checks if the difference of your EnumList and your VC is already blank so it does not trigger until all tasks are already created. You then add this Delete action as the last step in your loop group. Also, I only recommended that bec the Looper table in this video do not actually have any purpose other than to create the loop. If this is a table with a purplse, for example a Project table which has Tasks child records, then of course I wouldn't delete the project record after all tasks are created.

You bet!

Even if you tick allow other values in your [System], it will not allownusers to add a new system since the Valid-If expression will only allow those that are already existing in the table. Therefore, the method I've shown in this video will not work. But what you want is still possible using a not so straight-forward method. First, instead of using Valid-If in your [System], put it in the Suggested Values. This would still show the options saved in the Sheet, but still allow your users to enter a new system. Next, remove the Valid-If from your [Subsystem] and then use a SELECT() in the Suggested Values. SELECT( Dropdowns[Subsystem], [System]=[_THISROW].[System]) Lastly, do the same for [Item Name] but modify the select statement like this: SELECT( Dropdowns[Item Name], [Subsystem]=[_THISROW].[Subsystem]) Take note that if you ticked auto-complete other values, then other users will see the new items added as options next time. However, it will not save those options in the actual google sheet. If that is what you want, then use Ref type for your [System] column. Hope this helps.

@@FullOfSheets Thank you so much!!!! IT WORKED... I changed the [System] column type to text. As it was asking for values in ENUM. Is there any way so that the new item added it also reflects it in actual google sheet after saving?

@vedsahu5306 yes. As mentioned in my last comment, use Ref in your [System], remove the valid-if and Suggested Values. This forces your user to create new set of of combination of [System], [Subsystem], and [Item name].

@@FullOfSheets If I do that then the dependent dropdowns will not work.

It should. Or kaybe don't remove ylthe valid if. If all else fails, then you would have to change the Valid if of the succeeding dropdowns to SELECT() based on the newly added and selected [system].

Thank you, too, my friend. I'm glad you like my teaching style. 🤗

In a nutshell, this is what you will need: Users table must have these colum s Email, Name, Position, Team, Manager's Email You will then use security filters to filter out . Use LOOKUP() to identify the team lead's email and manager's email of the user in the Submitted By. You can use SWITCH() expression which is equivalent to "If, then, else-if, then, else-if, then, else". SWITCH( LOOKUP(USEREMAIL(), "USERS", "EMAIL", "POSITION"), "Admin", True, "Manager", LOOKUP([SUBMITTED BY], "USERS", "EMAIL", "MANAGER'S EMAIL") = USEREMAIL(), "Team Lead", LOOKUP([SUBMITTED BY], "USERS", "EMAIL", "TEAM") = LOOKUP(USEREMAIL(), "USERS", "EMAIL", "TEAM"), [SUBMITTED BY] = USEREMAIL() ) Hope this helps.

Ooh Thanks Man, you are my savior.. Its really solved my problems, I am lucky to see your channel

Glad I helped.

Thank you, I appreciate it. I have demonstrated the INPUT() function in this video. It's exactly the same. The dropdown will automatically show in the Input popup, if that column is already configured as a dropdown even if not in Input. You cannot just create the dropdown in the input popup. It must be a dropdown everywhere.

Also, the main ossue here is that AppSheet does not respect whatever you put as the Default Vakue in the Input setting. It does this before which is why I suggested to put "". But for some reason, it now disregards it and just uses the value of the first row selected as the default value.

Thanks for the sub!

My pleasure!

Thanks bro.

Thanks, kapatid! 🇵🇭

Did you try putting the break condition in all the actions in the group, as shown in the video?

@@FullOfSheets Yes condition is added in all three actions and thank you, I cracked my interview by watching your videos😀

Wow!!! That is awesome!! Thanks for letting me know. Congratulations!

Salamat, tol. Yan talaga ung para sakin, mga pinaka importante malaman e. Hindi masyado tinuturo ng iba.