Wondering how to reference a property from a field in the same table

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
  • (Edited)
Hi people,

I have just started using QuickBase but having coding experience. In my table, I have a column called Tours. When I click on Tours, it shows me Start Date/Time and End Date/Time. In the same table, I have an Available column and would like to populate it with 'Yes' when the CURRENT Date/Time is between the Start Date/Time and End Date/Time. 'No' if it's outside of the interval.

I would like to know how to reference those two properties of the tour field when writing a formula. I would also like to know which potential function call would guarantee that Available is updated automatically.

Thanks,
John B. 
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
I think this is what you are looking for:
If([Start Date/Time] < Now() and Now() < [End Date/Time],
  "Yes",
  "No"
)
However, you statements "I have a column called Tours. When I click on Tours" and "two properties of the tour field" leave me a little confused as you refer to Tours as  if it wasa field when I think you might mean Tours is a table.
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
Thanks for the quick response!

Sorry for the confusion, I'm learning the semantics still. My table is 'Tractors', and a field of the table is 'Available' and 'Tours'. This 'Tours' field is a relationship to another table called 'Tours' which has a Start Date/Time and End Date/Time.

So I would like to get those Start and End values for a formula in my 'Available' field. So some pseudocode:

'[Tours].StartDateAndTime ..... [Tours].EndDateAndTime'

Thanks again,
John
(Edited)
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
Update of the current situation:

So I decided to add an 'Available' field in my 'Tours' table that is populated based on Start and End time, which was successful. Thanks for the code snippet. (Although it would still be nice to know how to reference a field from another table)

However, I still want to populate the 'Available' field in my 'Tractors' table. Both of these tables have a 'Unit #' field, which would probably be the best way to retrieve if it is available or not.

The objective would be to get the Unit # from this 'Tractors' table, find the corresponding Unit # from the 'Tours' table, and populate the 'Available' field in 'Tractors' table to the same availability state in the 'Tours' tab.

Would this be the best route to take?
So:
1. Get Unit # from 'Tractors' table
2. Find Unit # in 'Tours' table
3. Get 'Available' from 'Tours' table
4. Set 'Available' in 'Tractors'

Thanks for any help.
John
Photo of John Barulich

John Barulich

  • 634 Points 500 badge 2x thumb
Another update:

Starting to realize I can probably create a relationship based on these values. Can anyone lead me in the right direction. Sorry to constantly change my ideas, I was thrown into this head-first and have never used QuickBase

Thanks,
John