How to validate columns while adding new record?

Ask for Help
1

I have created a leave form, an employee can apply for Casual leave only if he has balance in credit.

My tables are as below

Leave table

  1. Employee name
  2. Leave start date
  3. Type of leave (CL,ML,EL etc)
  4. No of Days

Employee Master

  1. Employee name
  2. Leave Balane CL
  3. Leave Balance ML
  4. EL Leave Balance (Earned Leave)

If an employee request for 5 CL, and if there are only 3 CL in his credit, 2 should be automatically allocated to EL.

How do I achieve this in Glideapps.
Please help.

2

Hey @edbs_India,

This sounds like an interesting use case. I’m circling in two of our talented Certified Experts, @Darren_Murphy and @ThinhDinh, who can certainly help you with this.

2 Likes
3

So in a case like this, you would want to add 2 rows to the Leave Table, yes?

  • One row with 3 days CL
  • One row with 2 days EL

I’ve done something similar to this before. It’s not straight forward, but it’s possible. Here is a general approach:

  • Firstly, you’ll most likely need to use a Custom Form for leave applications, so that you can pre-calculate the number of days for each leave type prior to the form submission
  • I would use a single row helper table, and build the form on top of that.
  • Initially capture all the form inputs in User Specific columns
  • Use the signed in UserID to build a relation to your Employee Master table. (NB. If your Employee Master table is also your User Profiles table, then you may not need this and the following step, as you’ll be able to access those values directly).
  • Use lookup columns to fetch the available balance of each leave type
  • Use the entered start date and end date to calculate the number of days being applied for (NB. It’s possible to take non-working days into account - weekends and public holidays - but the method for doing that is a mini-tutorial on its own)
  • Subtract the days being applied for from the available CL balance. If the result is a negative number, this is the number of EL days that will need to be applied.
  • Use a custom action on submit, with two main branches:
    – The first branch will add a single row to the Leave table (where the CL balance is sufficient to cover the number of days being applied for)
    – The second branch will add two rows (where there is insufficient CL balance)

Hope that helps.

3 Likes
4

Woohoo, @Darren_Murphy :clap: Thank you!

5

Will try this method