Hi everyone. I´m Francisco Moreno from México. Firstable, let me congrat Appsheet guys for so ergonomic & excellent made app platform, definitely you are rocking the world!!!
I need your help & support for the following:
I´m making an app for beauty shops in order to they can control and get following
their client/customer appointments. Practically, It almost has complete, however I have problems to find or configure a formula which not consider schedules (In hours) who has already select by another clients.
I have a google sheet with 12 sheets used as appsheet´s tables. Tables which bring and configure information to the main one are: - Schedule (named “HORARIOS”) and has 2 colums: first with datetime column and second with all possible workhours list by hours split it by 1 hour. - Spend (named “DURACION”) and has 2 colums: first with datetime column and second with a possible spend time list by hours split it by 30 minutes. - Attend (named “ATIENDE”) with 6 or 7 columns with general information of people who do services including a range of time work, example Sonia works from 8:00 to 14:00 hrs. - Appointmet type (named “TIPO CITA”) includes Make up, cut hair, paint hair, etc… - Service type (named “TIPO SERVICIO”) configure kind of services each appoinment type can have (for example; make up for weddings or social events, etc…), spend time required, cost, who from attend can do or not service, etc…
Main table is registers (named “REGISTROS CITAS”) and into this, clients select and configure all the information required to book their appointments. Into this table it has some main columns as: - Appointmet type (named “Tipo Cita”): Make up, cut hair, paint hair, etc… - Service type (named “Tipo Servicio”): A kind of service regarding the main type select, for example; make up for weddings or social events, etc… - Spend time (named “Tiempo Requerido”): Spend time to do service. - Attend (named “Atiende”): Person who make service. - Appointment date (named “Fecha Cita”): Date of required date. - Start Time (named “Hora Inicio Cita”): Time begin service. - End Time (named “Hora Termina Cita”): Time service ends. Start time + Spend time.
In order to avoid the appointments get cross each others, in column Start time I want to put through valid if a formula which just consider and show the times or availables schedules, this means, it has not be already selected by another client regarding to appoinment date and who attends. Maybe it sounds easy but the hard part is not to consider not only the start time and end time columns into a select expression, I need to consider not to include the range of time between start and end time which is the spend time column. For example, if a client had already select “SONIA” as attend the 10/01/18 at 11:00 hrs. to make a cut hair service which has 2 hours as spend time (means ends time is 13:00 hrs.) and another client wants to make a new appointment with “SONIA” too same day; column start time has just to show range of work schedule of “SONIA” without consider range of time from 11:00 to 13:00 hrs., this means values 11:00, 11:30, 12:00, 12:30 into column list has not to be shown. Besides, I need not to consider into column list too, schedules times which would cross regarding to a new appointment and its spend time required, for example; following the same example above and considering issue has already solve and the values of time already selected has not appears into column list, so values 11:00, 11:30, 12:00, 12:30 has not appears into column list, so the complete column list shows: 8:00,8:30,9:00,9:30,10:00,10:30,13:00,13:30,14:00,14:30 (Supposing “SONIA” works until 15:00 hrs). When a new client wants to make a new appointment and select same date with the same person attend and a service with 2 hours of spend time, if client would select 10:00, it would cross with the last client appointment because it has 11:00, 11:30, 12:00, 12:30 and new client would have 10:00, 10:30, 11:00, 11:30 crossing between, 11:00, 11:30; so I need not to include by the case of new spend time´s appointment would be 2 hours the following lists values too: 9:30,10:00,10:30, not considering as total: 9:30,10:00,10:30, 11:00, 11:30, 12:00, 12:30 for the new appointment and showing in column list 8:00,8:30,9:00,13:00,13:30,14:00,14:30.
I´ve already solved the issue commented first above with the following valid if formula: =SORT(SELECT(HORARIOS[Horarios],AND([Horarios]>=LOOKUP([_THISROW].[Atiende],ATIENDE,Atiende,Horario Inicio),[Horarios]<LOOKUP([_THISROW].[Atiende],ATIENDE,Atiende,Horario Final)),TRUE) SELECT(REGISTROS CITAS[Hora Inicio Cita],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE) SELECT(REGISTROS CITAS[Duración 1],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 2],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 3],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 4],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 5],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 6],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 7],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 8],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 9],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)-SELECT(REGISTROS CITAS[Duración 10],AND([Fecha Cita]=[_THISROW].[Fecha Cita],[Atiende]=[_THISROW].[Atiende],OR([Status Cita]=“CONFIRMADA”,[Status Cita]=“REAGENDA”,[Status Cita]=“LISTA DE ESPERA”,[Status Cita]=“REQUIERE ANTICIPO”)),TRUE)+SELECT(HORARIOS[Horarios],AND([Horarios]>=LOOKUP([_THISROW].[No. Cita],REGISTROS CITAS,No. Cita,Hora Inicio Cita),[Horarios]<LOOKUP([_THISROW].[No. Cita],REGISTROS CITAS,No. Cita,Hora Termina Cita))))
But second one issue not, any idea or suggestion to do it???
I realy appreciate your help.
Regards.
LinkedIn
Twitter
