Query rows via API

[evazivanovic]

Hey guys,

I’m using Make to run an automation and I am having trouble querying the data. I think this could probably be done without Make too, but that’s not the point.

Here’s the use case:

• The user submits an absence. This absense should be linked to the right annual leave record.
• If the annual leave record does not exist, it should create a new one if it fits certain conditions.

Here’s an example.

Josh has an annual leave of 25 days which renews yearly on Jan 1st. This leave is always valid from Jan 1st to Dec 31st.

However parental leave is valid from the day the parental leave absence is submitted.

So if I submit parental leave today, and I don’t have an active parental leave record currently, a new one should be created with validity until July 23rd 2025.

So basically I am using a few different filters:

• user email
• absence type
• leave dates

I don’t want to iterate through the entire table, I would just like to search for records that fit my criteria.

How can do that?

I hope that wasn’t too confusing

[ThinhDinh]

So your data would look something like this?

1. Annual Leave Records:

id	user_email	leave_type	total_days	start_date	end_date
L0001	josh@example.com	annual	25	2024-01-01	2024-12-31
L0002	josh@example.com	parental	365	2024-07-23	2025-07-22

2. Absences:

id	user_email	absence_type	start_date	end_date	annual_leave_record_id
A0001	josh@example.com	annual	2024-08-01	2024-08-05	L0001
A0002	josh@example.com	parental	2024-07-23	2024-08-22	L0002

[Darren_Murphy]

@evazivanovic

I don’t want to iterate through the entire table, I would just like to search for records that fit my criteria.

How can do that?

You can’t, unless the source table is a Big Table.

But looking at your description, I don’t think you need the API. You should be able to handle all the logic within Glide (I suspect that’s where @ThinhDinh is going with his clarifying question).

[evazivanovic]

@ThinhDinh close but not quite. The structure overall looks correct, though, yeah

The parental leave depends on the number of children the employee has under 12 years of age (norwegian legislation). It could 10 days it could be 12 etc.

So basically what I need to do is:

1. Check the type of leave request
2. Check if parental leave record exists
3. If not, check how many children under 12 the employee has
4. Create a leave record with validity of 365 days and add the respective number of available days to the record
5. Link the leave request to parental leave record

[ThinhDinh]

I think you would have to create a custom form for that.

How do I create a custom form?

I’m just dropping this here so I have something to refer people to when they ask. This is a simple concept app (copyable), that demonstrates the following techniques: Building custom forms for adding and editing list items (as an alternative to the native forms provided by Glide) Preventing duplicate new entries using User Specific Columns and Relations Enforcing mandatory input items The use of visibility conditions and user specific booleans to control user flow There is nothing particular…

• Create an If-Then-Else column in your User Profiles table called “Parental leave days”. If “children under 12” is over X, then 12 days, else 10 days, structure your logic around this part.

@evazivanovic

The parental leave depends on the number of children the employee has under 12 years of age (norwegian legislation). It could 10 days it could be 12 etc.

• Back to your custom form’s helper table, you should have user-specific columns to hold the user’s type of leave request.
• Create a query column targetting the parental leave records table, filter by the user’s ID/email, and the type of request being the same as the one in your form.
• Have a submit button in your form. Have a custom action with as many branches of logic as you need. One should be “If query column is empty”, then add a parental leave record, before adding a leave request. Else just add the leave request.