I want to populate a dropdown with Employee N...

(tvandang) #1

I want to populate a dropdown with Employee Name. If i select an employee name from this dropdown list, i want to get the associate Employee ID so get more information about this employee from the connected tables.

How can i achieve this? I do not want to use employee name as the primary key.

(Segolene Sansom) #2

so the app i shared to you wasn’t the one you sent to me as the link didn’t work. the one i created and shared should be in your app gallery under shared apps. that’s the one you need to copy

(tvandang) #3

I am new to the interface but I see where it is now:-)

I noticed that create the spreadsheet formula using SUMIFS.

I will look into this syntax furthermore.

If you create formula in the spreadsheet instead, and you create more entries (rows), wouldn’t you copy down the formula as far down as possible?

Is this a better practice is to write the syntax or formula in the spreadsheet rather than the expression

in the app?

?

(tvandang) #4

If I add a row in the spreadsheet and have a formula to calculate the value for that cell column and I added another row, I need to copy that formula to the same cell column in the second row. Am I right?

(Segolene Sansom) #5

appsheet automatically copies the formula down when it creates a new row. no need for you to do it manually. For certain things i prefer making the formulas in excel even though it does require syncing to see the results because for some things (especially if i have mass data to add) i might decide to add the data straight to the excel which means i know all the formulas will still work then without having gone through appsheet.

also i believe that if you want to keep the results of a formula for the future you should do it in the spreadsheet whereas if the result of the formula is only for show then it is better to do it in appsheet.

(Segolene Sansom) #6

if you manually enter data in then yes you will have to drag the formula down but if you do it through appsheet then you won’t have to.

(tvandang) #7

Thank you so much.

Now I have a different perspective of create the sample app. It makes a lot of sense now. I appreciate that you figured out how my sample app worked:-)

it was just a test trying to figure it out. I think it is important for me as a start is to create the relationship between the tables and how to access the data to create the different designs and navigation in the app.

(Segolene Sansom) #8

you are most welcome! happy to help! I’ll go ahead and delete my version of the app now so you can go wild and play. don’t hesitate to post other questions!

(tvandang) #9

I did not know that at all. I will create my formulas in the spreadsheet instead from now on. I will brush up with my excel formulas. Do you do SELECT () statement in the spreadsheet or in the app?

(Segolene Sansom) #10

what do you need select for?

(tvandang) #11

Thank you so much.

It was very kind and patient of you to help me with this app. I will sleep well tonight without worries. Thanks again!

(tvandang) #12

Like a customer can have multiple addresses and you only want to bring a list of addresses to be displayed, and or restricted by state or zipcode. Just as example. Am I right?

(tvandang) #13

I know now that if I create a REF column connecting a customer table to address table(one to many relationship), appsheet will automatically create the list of addresses for that customer.

(Segolene Sansom) #14

then you would use it in appsheet BUT if a customer could have multiple addresses i would create a seperate table for the addresses and ref it back to the customer table. and if that was the case i would add a column called primary with a yes/no type of column which would allow you to pull the information through if you needed to copy the address somewhere quickly

(Segolene Sansom) #15

@tvandang yeah you got it!!! well done!

(tvandang) #16

I had 2 columns in the table employee, cash percentage and check percentage and are created when an employee is created. So when I create the payroll table, I didn’t not want to create these columns again since they can be accessed from the payroll table via employee table. However I do notice that you create the columns again in the payroll table and use vlookup to access these values. Is that how data being access?

(Segolene Sansom) #17

you don’t have to create them you can integrate them direct into the formulas for cash and cheque however i like spliting things up in smaller chunks when possible so that if any errors pop up in the future it is easier to find the source but that is a personal preference. and as these columns are formula based, you can hide them in appsheet but allows you in excel to check that everything is working correctly

(tvandang) #18

I guess using vlookup to pull desired data across all tables in the app that have common fields(columns) is the best practice. I was struggling with that but I realize the way you are doing it, makes it easier to understand when I create the formula in the spreadsheet.

(tvandang) #19

It is makes a lot of sense now. What are your most used excel formulas for all your apps?

Vlookup, sumifs, ect? Just curious so that I can look them up and learn them.

Are you french? I noticed that you spell check as cheque? :slight_smile:

(Segolene Sansom) #20

yeah french background and studies but in australia 23/28 years. most used excel formulas is a hard one, i use so many… definitely, sumif, sumifs, vlookup, round, roundup,rounddown, if,or,and,iferror,… for reports i also use arrays…