Hi all,
I have two tables, โAppointment detailsโ and โHair Servicesโ.
Hair Services has the following column:
[Service]
[Estimated Time]
I want to create an EnumList in the โAppointment detailsโ table which adds multiple services from the โHair Servicesโ table. Also, I want to calculate the total time for each service and store it in a virtual column in the โAppointment detailsโ table.
The appointment table should have the following.
Service list: (Enum list form Hair Service table)
Total time: (VC sums the estimated time for each service added)
Is there an app formula that I can use?
I see that part of this question has already been answered previously using SELECT and IN, but for some reason, I get an error (Parameter 2 in IN is wrong).
Any help is really appreciated. Thanks in advance.
Thanks,
Ankit
Solved! Go to Solution.
For that u have to create a child table using isapartof in the table โฆ
First create a new table with columns like unique id, order id(this will be the key of main table), Service REQD, time taken,
Order id should be made ref and isapartof should be turned on
Then in the main table add a new column naming it total time reqd and put formula
Sum(select(childtablename[time taken],[order id]=[_thisrow].[keyofmaintablecolumheader]))
This will do the job
in Service List Table make data type enumlistโฆ
and in the data validity of this column header put the below expression
Hair Services[Service]
where Hair Services refers to the table and Service refers to the column in the table Hair Services
It should work without any issue
Thank you for your response. The issue is, I need to reference the Service list column to Hair Services[Service] column because I also want to calculate the estimated time of each service that is added, based on the column Hair Services[Estimated time].
in that case u have to first create a child table to add service listโฆin the child table u can add any no. of services the customer wants to use and in the parent table you use an expression to calculate the total time reqd for this customer.
Thank you for your response. I have created the โService listโ as a ref to โServiceโ in the โHair Servicesโ table and have modified the virtual column in the โHair Servicesโ table with the following app formula.
SELECT(Appointment details[Uniqueid], IN([_THISROW].[Service],{โService listโ}))
I assumed that this will generate an enum list type reference for the Service list column but it just creates a one to one reference. How can I add multiple Services to one appointment?
Hope my question is clear.
Thanks in advance
For that u have to create a child table using isapartof in the table โฆ
First create a new table with columns like unique id, order id(this will be the key of main table), Service REQD, time taken,
Order id should be made ref and isapartof should be turned on
Then in the main table add a new column naming it total time reqd and put formula
Sum(select(childtablename[time taken],[order id]=[_thisrow].[keyofmaintablecolumheader]))
This will do the job
That worked. Thanks a lot.
Ankit
User | Count |
---|---|
40 | |
34 | |
30 | |
23 | |
17 |