linking forms more questions

[stephanie crocker]

Continuing to build a crop management database.

I have a crop list form.
I have a variety form. One crop can have many varieties.

Successfully linked the two forms so I can add multiple varieties of a particular crop I am planting. And I selected “show inverse relationship” so I can see the crop that corresponds to a particular variety on the VARIETY form.

Now I need a third form, the GREENHOUSE form so I can enter the date, then enter the crop, and variety from the two other forms.

In my GREENHOUSE form, I have
– seeding date
– variety name

I’m curious whether I need a primary key in the GREENHOUSE form as I seed multiple varieties on one day. OR would I just put them all into one record for the one date?

If I have already added the variety in the VARIETY FORM, the corresponding crop shows up, but if I HAVEN’T, I don’t know how to navigate without having to jump over to the VARIETY form then add the variety and link it to its corresponding CROP from the Crop form.

In other words, from the GREENHOUSE form, I want to also be able to add a variety.

I’m also curious about best practices in my database schema so to avoid what seems to be multiple empty records when I create a new linked record. (I wish I could describe this better but I’m having trouble identifying what I am doing that is causing this)

[Sam Moffatt]

I think it’s more up to personal preference. If you want to add notes per variety that you plant or extra metadata specific to that day and variety, then you might want to use a record per day per variety. You could do a record per day and then use the link to select which varieties you planted that day. If you don’t track any other metadata with your planting then a single record per day might work (maybe a free form notes field).

If you did per day per variety then you want to do a 1:M relationship and enable “show inverse relationship” checkbox on the Link to Form field then it will create a Link From Form field. On the Link From Form field, there should be four buttons: the first is an add button (it will create a new record and take you to the form), second is an unlink button, third takes you to the linked record and the fourth lets you select a new record. If you click the first button to add a new record, you can populate it from that screen and then jump back to the previous record with the back button that should appear at the top of the record next to the default layout selector. When you jump back it should have that linked already.

If you wanted to do one per day then I’d do a M:M relationship it’s a similar process though you get a table view on both sides when you enable the “show inverse relationship”. It’s a little easier to do data entry with the tables for new records because you can do that inline or you can create a record and go directly to it (second option under table) to fill it in.

In general you don’t need a primary key in the traditional database sense mostly because internally Tap Forms has unique identifiers, you only really need it for your own purposes. You can use calc or script fields to help you compact multiple fields into a single field to make it easier to pick out of the record selector.

One other random thought is that if you only have a name for the variety and no other metadata, you might want to look either using autocomplete for it or at a pick list for it. Autocomplete does what it says on the tin and pick lists can be configured to refer to a particular field so you can get a sort of autocomplete that way. Pick lists have slightly more UI options and you can also do stuff like record highlighting with them.

If you can take a short video and upload it to YT then you can link to it. If it’s quick enough and a small enough window area then it’ll fit into the file upload limit for the site. You might have found a bug in the system which would be a cool win as well :)

[stephanie crocker]

Super helpful. I am now zooming out realizing I needed to spend some time learning logical database design before jumping in without a plan. I started to map out my relationships on paper way more thoroughly and see there are still a few loose ends I need to figure out.

Once I get that finalized, I’ll see if I can recreate the issue regarding the extra records. I imagine it is an error in my own development process ;)

[Sam Moffatt]

If you’ve not seen it, there is the concept of database normalisation which is the process of thinking about your fields and structuring the database. I will say that you might for various reasons want to replicate fields and the cool thing is that calc and script fields can help you copy data without having to manually maintain them.

Good luck mapping it all out :)

[Author]

Posts