Database relationships - not seeing T/F field

[MikesClub]

I think I’m just not understanding how relationships between collections work.

Collection 1 (LibraryItems) is a list of items in my inventory. There are only 2 types of items in the inventory: Movies and Tools.

There are 2 True/False fields in this collection, to identify whether an item is a tool or a movie (Tool? and Movie?).

A record in collection 2 (LibraryRecord) is created whenever someone borrows an item.

A relationship exists between these two collections.

So here’s my problem:

In a list of a user’s borrowed items (which is just a list of that user’s LibraryRecords) I want to show whether each item is a tool or a movie (because the terms of borrowing will be different). But I can’t find the Tool? / Movie? identifiers via magic text. Those T/F fields don’t seem to carry over from the LibraryItems collection to the LibraryRecord collection.

I tried creating identical T/F fields in the LibraryRecord collection, but they don’t populate with data from the LibraryItems collection.

What am I doing wrong here? Isn’t the relationship between collections A and B supposed to make data from A available in a list of collection B?

I hope someone can advise me on this. I’m confused!

[dilon_perera]

Hi @MikesClub,

You mean you don’t see the true./false properties when going to add them from magic text in the text component? Then it’s true that they don’t show. For this you need two texts with visibility conditions.

Check this : Movie or Tool.mp4 - Google Drive

Thank you

[Bobby]

Whenever I have categories as you’ve explained I just make a separate category collection, it seems to work well for me and makes it a bit easier for sorting.

[MikesClub]

Thanks Dilon. I’m studying your video.

What I’m actually trying to do is display a late fee, for items returned late. $1 per day for late movies, and $5 per day for late tools.

I have a screen that shows each user’s borrowed items. The list shows the name of each item, it’s due date, and whether or not it’s overdue. Then there is another text box with a custom formula that’s supposed to display the current late fee, depending on whether the item is a tool or a movie. The formula will essentially be x either a 1 or a 5, based on the t/f properties.

[dilon_perera]

This video will help you too! : Loom | Free Screen & Video Recording Software | Loom

[MikesClub]

Dilon, I followed your instructions (I think) exactly, but my custom formula seems to work differently than yours.

I created a number property called “Category” in the LibraryItems collection (using 1 and 5 like you suggested).

Then I created a text field in the Returns screen (which is a filtered list of LibraryRecords) with the custom formula to calculate late fee.

But, according to your video, your last nested menu ends at “Category ID”, but mine has one more submenu. The one showing Sum, Average, Minimum, etc. I can’t just choose “Category”. I tried using Sum, but formula doesn’t do anything.

Why are my options different from yours?

[dilon_perera]

Hi Michael,

Seems like you have a many to many relationship. So a record can have many items?

Thank you

[MikesClub]

I hadn’t considered that. When I originally created the relationship, I wasn’t sure how the whole thing would work. I originally expected that a record would contain many items.

So it looks like the relationship is currently 1 LibraryRecord to many LibraryItems. I’m going to delete that relationship and recreate it the other way around. It should be 1 LibraryItem to many LibraryRecords. A ladder can be borrowed and returned multiple times, and this should allow me to track the history of each item’s usage over time.

I’ll play with this and get back to you.

[MikesClub]

Just keeping this thread open for a few more days. I haven’t had time to experiment much with this solution yet. Dilon, I will get back to you soon, I promise!

[MikesClub]

Ok, I’ve been trying to fix this, but I can’t get it to work. I’m sure the problem is in my relationships, but I might be wrong.

First, I made a few changes.

In the LibraryItems collection, I added a number property called LateFee. This replaces the Category property.

If the item is a tool, the LateFee is 5. If it’s a movie, the LateFee is 1.

I also changed the relationship as I described above. A LibraryItem can belong to many LibraryRecords. The LibraryRecord is a record of each time an Item was borrowed and returned. So an item called Hammer can be borrowed, returned, and borrowed again, multiple times. And a new LibraryRecord is created each time the item is borrowed.

But a LibraryRecord can have only 1 LibraryItem. If a user borrows a hammer and a ladder, 2 LibraryRecords are created (one for each item).

When an item is returned late, the user must pay a LateFee, which is either $1 or $5, depending on whether the item was a tool or a movie. As I mentioned above, the LateFee is stored in the LibraryItems collection.

So, here’s my problem:

I know how to create a custom formula to calculate the LateFee multiplied by the number of days late. That’s not a problem.

But I can’t get the LateFee to appear in my text box. Please look at these two screenshots:

I can select any of the properties under Current LibraryRecord, and it will appear in my preview.

But…

If I select a property under Current LibraryRecord > LibraryItem, it does not appear in the textbox in the preview. I’ve tried it with every property. Nothing.

Is this a relationship error, or am I missing something else?

I can share the updated app with you again if necessary. What do you think?

[dilon_perera]

@MikesClub

But I can’t get the LateFee to appear in my text box. Please look at these two screenshots:

This is because the late-fee property created in the Library Items collection and you can access it from Current LibraryRecord’s > LibraryItem’s > Latefee ( like the second screenshot )

@MikesClub

If I select a property under Current LibraryRecord > LibraryItem, it does not appear in the textbox in the preview. I’ve tried it with every property. Nothing.

Did you checked in your LibraryRecords database if a item is attached to a record? Maybe you haven’t added the current LibraryItem on your Create action?

@MikesClub

I can share the updated app with you again if necessary. What do you think?

Yeah that’s a good idea! Instead of cloning add this email ( pereradilon24@gmail.com - in your editor gear icon > app access section and that email ) on the app access section and then I have the access for your app. Instead of cloning this is easy!

[MikesClub]

Dilon, I gave you app access.

I won’t touch the app for a few days so you can have a good look around.

FYI, this app, called Library, shares a database with my main app called Mike’s Club. That shouldn’t be a problem for you accessing Library app. The Mike’s Club app will be used by my customers (club members) and the Library app will be used by my employees to manage the store.

A few things about the Library app. It will primarily be used by my employees to manage the borrowing and returning of items in our library of things.

The Library app also has a screen to show all overdue items (not specific to any particular member).

And on the home screen (which looks like a profile screen) there is a text box to show whether the user’s membership is current, expired, or nearing expiration.

If the membership is current, he or she can borrow new items and return borrowed items, so both the ADD ITEMS and the RETURN ITEMS buttons are visible.

If the membership is expired or nearing expiration, the member can only return items.

And there is also an ALERT warning to the my employee know that this member either has unreturned items or still owes late fees.

Eventually we will use a QR code scanner instead of manually typing in ID numbers. Each item in the library will have a QR code label, and each member/user will have a QR code either on their app or on a physical ID card. Currently we just type in the member’s ID number and the item’s inventory number.

The idea is that we will scan a member’s QR code, and then scan the QR code labels on any items they are borrowing. This is what we create a LibraryRecord.

When an item is returned, its LibraryRecord will be marked “returned”, but the record will not be “cleared” if a late fee is still due. So it’s possible for an item to be returned but the record will still show an unpaid late fee. The record will only be cleared when an item is returned and late fee paid (if late fee is appropriate).

Cleared LibraryRecords will remain in the database, allowing us to see the usage history of that particular item, and to see each user’s history with regard to repeated lateness, a history of damaging items, etc. That’s why there is a NOTE field when an item is being returned.

All of that is more or less what this app is all about.

[dilon_perera]

Hi Michael,

Once you added Current LibraryRecord’s > LibraryItem’s > Late fee in the magic text the reason that it’s not displaying because your all records LibraryItem property is empty!

And in your Create action it’s empty too!

And I updated them and now the issues should be solved! Check again and let me know!

Thank you

[MikesClub]

Yes, that seems to have solved the problem!

Actually, I do understand why this happened, and that leads to a question.

When you create a new collection, the NAME property is automatically created (and locked).

So, when I created the LibraryRecord collection, I assumed the name of the item had to go there.

Then, when I added a relationship to the LibraryItems collection, that relationship added its own NAME property. I left that one blank because I didn’t see a need to fill both fields with the same data.

So, here’s my question: Do I need to put anything in the default NAME field? Or can I just leave it blank and move it to the end of the record?

I think this is like the USERNAME property in the Users collection. Many Adalo developers choose to leave it blank.

Am I understanding this correctly? And can I just not use the default NAME field in my LibraryRecord collection?

[dilon_perera]

@MikesClub

So, here’s my question: Do I need to put anything in the default NAME field? Or can I just leave it blank and move it to the end of the record?

Yeah, It’s not required! You can add the item’s name there or not. Or you can move one of the number or date and time or true/false property to top and then you can delete that Name text property!