Database type relationships

Need help Database
1

Hi,

I am really struggling to setup the database structure to work as I would like.

I have a relatively simple database structure;

Type ‘Desks’ which include the following static fields which relate to a repeating group;

Desk name.
Desk Number.

Type ‘Booked’ for the variable fields which include.

Booked date.
First name.
Surname.

I cannot work out how these link together effectively. When I just had the ‘booked date’ it was simple enough as I created this as a list and created a condition that checked if the list contained that date and if it did displayed ‘Unavailable’ in that repeating group cell. When I try and introduce the First name and surname this does not always work as the date may be in the list and the first name so shows as ‘unavailable’ for every date rather than just the one. I have read through everything on this forum regarding relational databases and other options but still don’t seem to be able to achieve something that most might find very straightforward.

I hope my explanation makes sense. Can someone please advise how I could achieve this please?

The editor link is here for a copied version of the app;

The workflows are on the ‘When button booked is clicked’.

The ‘Unavailable’ is a hidden text box in the repeating group with various conditions on it.

Thanks in advance

John

2

Without knowing your use case, the typical design to solve this kind of problem would have three tables - one for Desk, one for Person (or Student) and a third to capture the association of a student with a desk along with a booking or reservation date. The fields and tables would look something like this:

Desk

  • Desk Name (text)

Student

  • Student Name (text)

Reservation

  • Student (reference to other table/Thing)
  • Desk (reference to other table/Thing)
  • Reservation Date (date)

This eliminates the potential for duplication and gives you one place that can be searched for reservations. You would ‘Do a Search’ against the Reservation table and assign the desk and table.

Hope this helps.

3

Thanks. It’s the reference but I’m struggling with. How/where is that done please?

4

You do it by setting the data type to be the name of the other element.

Assuming your “Student” is a user. Then you don’t need the “student” data type.

So you have your Desks data type. Which is the static set up of the desks.

And you have a “Desk Booking” which has a Desk field (of type Desk) and a Date.

You doni’t need to store the Desk fields on “Desk Booking” as you can just reference it from the Desk Booking. So … Desk Booking’s Desk’s Number.

Same with user names. Desk Booking;s Created By’s First Name.

You can link the data types together this way pretty seamlessl;y.

5

Nigel

Thanks. I have setup as you have suggested but still have the same issue. I have some text that (should) appear to show when a desk is booked or not. The condition I am using is as follows;

Condition
Condition691×628 68.3 KB

As you can see in the image below, the ‘Unavailable’ text shows for 3 desks, when in reality these are booked on different dates. The condition is actually working correctly as both the desk number and the date are in Desk bookings, what I’m actually trying to achieve is that desk 3 does not show as unavailable as it’s booked only for the 22nd, not the 21st. Does that make sense?

Unavailable
Unavailable2882×749 114 KB

6

In your example here

How do you define the name of each desk when it’s type is desk, not text? I keep running into this error when I have a field with another type.

7

Here:

Q: Where is a User’s name?
A: In a text field on a User object. (Possibly called Name, eh?)

Now, where would you put a Desk name?

8

For anyone else who found Keith’s sarcasm unhelpful and already has these fields on each data type (thing), but is running into issues trying to use one data type as a field of another data type to link the two data sets, you can label your data type (thing) using “primary fields” so that it doesn’t just show the unique ID on the other data type’s list.

image
image708×234 6.79 KB

You can find it in the “app data” tab of data. The primary fields pop-up is where you set the field label you want to display (should be unique), so that you see “company 1” (name) not “1544913740854x649…”

I’m still working on getting multiple data types to properly populate the dynamic choices dropdown based on user and privacy roles, but thought I’d share what I’ve learned so far.

9

So that is what the primary fields are for, check!
I just used the UID that came from the data field linking the tables to search for the name.
This is definitely easier.