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

[tvandang]

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.

[tvandang]

@Segolene_Sansom

[tvandang]

@Segolene_Sansom

[Segolene_Sansom]

ok first thing in your Timesheet table, i would make your timesheet ID a datetime column not just time as what happens if someone manages on 2 different days to hit new at exactly the same time… the datetime will allow for this to happen and not create a bug in the system

[Segolene_Sansom]

ok so where it says text nxt to DRP employee select REF instead.

Then click on “show all properties just under the table name” Then click on the new column “Type Qualifier” next to the DRPEmployee row.

In the popup that appears, select the employee table. Then make sure you save.

[tvandang]

@Segolene_Sansom I can make this work if i make the EmpName in the Employee table as the primary key but that is not good practice since employee name can not be UNIQUE. that is why i wanted to populate the dropdown list that can have both columns, EMPNAME and EMPID but the only column that shows in the dropdown will only be EMPNAME.

[Segolene_Sansom]

then in your sumif formula, you should be able to add [fkEmpID]=[_ThisRow].[DRPEmployee] to your conditions

[Segolene_Sansom]

I just quickly created the app per the way you have it structured and my comments above and it works. On the left is the excel where the data was stored to and you can see it is the id that is saved whereas on the right my selection is by the actual names.

[Segolene_Sansom]

if you’d like i can send you the excel file and transfer the app to you?

[tvandang]

@Segolene_Sansom so what value will this syntax output?

is it a EMPID or EMPNAME?

[_THISROW].[DRPEMPLOYEE]

i tried to edit expression: =SUM(SELECT(TIMESHEET[INCOME], AND([DATE]>=[_THISROW].[DATEFROM], [DATE]<=[_THISROW].[DATETO], [fkEmpID]=[_THISROW].[DRPEMPLOYEE])))

this syntax is valid but it produced the same data as if i were to remove [fkEmpID]=[_THISROW].[DRPEMPLOYEE] from the expression. this added expression pulls the same INCOME amount for all employees and not the selected employee.

so the AND is that it has to be within a date range and it is equal to the selected employee.

[Segolene_Sansom]

what is your appsheet id and email? I’ll transfer the app and data to you. Personally for this kind of thing i prefer using the inbuilt excel/google sheets formulas as it will keep the data whereas the appsheet formulas are good for display purposes but wont write the value into the spreadsheet.

[Segolene_Sansom]

@tvandang i am just a community member -

ok simpler method, in column type select ref

then in the type qualifier select your employees table. In the employees table, check the checkbox for label on the employee row and the one for key next to the employee label row

[Segolene_Sansom]

@tvandang That syntax will get the employee ID

[tvandang]

@Segolene_Sansom i am not sure what my appsheet id is.

is it the appsheet id for this app or the appsheet id for the account?

(google) Account ID: 667805

link to my appsheet app: appsheet.com - Sample mobile apps for common scenarios

this syntax [fkEmpID]=[_THISROW].[DRPEMPLOYEE]

the column DRPEMPLOYEE is a REF connected to EMPLOYEE table.

correct me if i am wrong.

the reason is that the dropdown list is showing the employee name is since it is a REF and i had checked the checkbox in the employee table for the column EmpName?

since I set the [fkEmpID] to [_THISROW].[DRPEMPLOYEE], appsheet knows what column in EMPLOYEE table to match?

[fkEmpID]=[_THISROW].[DRPEMPLOYEE]

i really appreciate your help.

i just got stuck on this problem and I can’t continue to create the app the way i wanted.

I am still a newbie.

Sample mobile apps for common scenarios appsheet.com

[Segolene_Sansom]

that was exactly right! to transfer it across i need your email address too. All good i was a newbie at the beginning of the year! community was a huge help when i couldn’t find the right documentation

[tvandang]

@Segolene_Sansom tvandang@gmail.com thanks!

[tvandang]

@Segolene_Sansom are you an app developer yourself or just someone who is interested in learning how to like me? just curious.

[Segolene_Sansom]

ok i’ve shared it with you as a coauthor, now what you want to do is when it appears in your app, create a copy of it which will allow you to have it as yours and modify as need be. let me know when you;re good and ill delete the app on my side. no worries

[Segolene_Sansom]

im someone who is interested. im actually a hospitality consultant and have convinced my company to switch over so im building about 6-7 different apps now on top of my regular work to facilitate our work life. but i like doing vba in excel and word and ive dabbled in salesforce as well as a bit of html so i can pick things up quite quickly nowadays

[tvandang]

Wow. As a hospitality consultant, you do have quite a technical background. That is quite impressive. I am happy that you are creating apps with a purpose. I want to do the same but struggling a bit:-(

[Segolene_Sansom]

well let me know if you need any help happy to do so. sent you an email so you have mine as well

[tvandang]

@Segolene_Sansom i am a bit confused how things are shared and you are a coauthor

so that means that you now have access to this app yourself?

did you make any changes?

editing this app looked the same as before.

how do i see that you are now a coauthor?

i do see an option in

the app under USERS that i can invite someone to this app.

[Segolene_Sansom]

once you have the above you can use virtual columns with formulas [lookup column].[Phone Number] for example - the lookup column described above must be a real column though

[tvandang]

@Segolene_Sansom thank you so much for your quick reply. i am very sorry for my lack of knowledge.

when a checkbox for a column as LABEL is checked, what does that do for that column?

i have EMPID as the key and EMPNAME checked as label.

these columns are in my EMPLOYEE TABLE.

In my PAYROL table, I created a FORM.

I have a column that i called DrpEmpName to list all the employee names.

If I select, the employee name from the list, I need the employee id for this employee name so that I can calculate the payments for this employee within a date range.

an employee gets paid every day.

would you be able to look into my appsheet ?

[Segolene_Sansom]

so my understanding of it is that in a ref type column, the display that is used is the one marked as label in the referring table. however if you look back in your excel table, the data that gets saved is the actual id of the same row.

i can’t look into your appsheet as i’m not part of the appsheet team. but happy to help any way i can

where do you pull the information from for the calculations?

are they done in excel or in appsheet? could you post pics of what you have?

[tvandang]

@Segolene_Sansom thank you your willingness to help. i have 4 tables.

i will add my each employee and each employee will have many entries in the timesheet table. so it is a one-to-many relationship.

So the PAYROLL table is basically used to generate a report.

So what payroll does is that I will select the EMPLOYEE NAME, TODATE and FROMDATE, to calculate the amount of income(PAYMENT) that this employee generate with the date range for that customer.

I was able to find the total payment but not BY EMPLOYEE, only by date range.

=SUM(SELECT(TIMESHEET[INCOME], AND([DATE]>=[_THISROW].[DATEFROM], [DATE]<=[_THISROW].[DATETO])))

so i need to get the EMPID when I select from the dropdown list(Employee[EmpName]) that only shows the employee NAME.

I can not use this name to match it in the TIMESHEET Table since it has a foreign key EMPID to the EMPLOYEE table. So i can not use the employee name to match with the EmpID in the timesheet table.

i have several screen shots and I realize that i can only upload 1 picture:-(

[tvandang]

@Segolene_Sansom

[tvandang]

@Segolene_Sansom

[Segolene_Sansom]

use

an enum with a ref type - in the employee table use the emloyee name as label and employee id as primary key

[tvandang]

I am sorry but i do not understand that.

setting that field as text or enum, do i not have to set in the validif to EMPLOYEE[EMPNAME] to pull the list of employees for this field?

How can i create a dropdown list to display only the name but its associated empID is hidden.

can you perhaps look at my appsheet?

do you guys provide a service just to have someone help with questions and answers?

[tvandang]

@Segolene_Sansom

[tvandang]

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]

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]

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]

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]

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]

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]

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]

what do you need select for?

[tvandang]

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!