Help with Basic Linking

[stephanie crocker]

So close…but can’t quite grasp my ideal relational database structure

I’ve got two forms
1. crop list. contains basic crop info
2. field log. contains log of what happens in what bed and when. has date in, date out fields.

One bed in the field log can have many different crops at different times.

To create a relationship between these two forms, in the “crop list” form I added a linking field to the “field log”. From within the “crop list” form, I am able to add planting dates and this creates a record in the field log.

So if I pick another crop from the crop list, it creates another record in the field log.

I watched the video several times and when I click on the plus button in the “crop list” linking field, it doesn’t bring me to the “field log”.???

What I want is to be able to quickly view the field log and see historically (and chronologically) what has been grown and when. Instead I have to click on multiple records to see what was planted and when for each bed.

Am I doing this right?

[Sam Moffatt]

I’m not sure I follow but I’ll make some suggestions and you can let me know if it gets you closer to where you want to be.

In the field log, I would use a pick list for your “field name” field to make it easier to select and ensure consistency. I’m assuming there is a “field name” field that you’re using to uniquely identify the fields and I’m going to keep that assumption moving forward.

The simplest thing is that you can set your sort order in the form details, you can set that to be based on a planting date in your field log. You can group by the “field name” field and then if you enable section headings you can jump to the different fields. This will work in either the single column list view or the multicolumn list view. One extra piece is that the multicolumn list view can be modified to maximise the spreadsheet view and hide the default layout as well if that’s easier.

If the grouping doesn’t work for you, then you can create saved searches for each “field name” that you have. I would guess you should be able to do this as is with your structure and it’d give you entries on the form list underneath the form name to filter each of the field logs. The downside of this approach is that you need to create a new search filter for each field you maintain. Saved searches can have their own sorting as well so you could sort the fields differently which may be an upside or an annoyance.

One final option would be a field list form as well since you’ve got a crop list and a field log, a field list would let you do the link from the field:

• Field List links 1:M to Field Log
• Crop list links 1:M to Field log

Field list is your definitive entry on what a field is and then the log are the sub entries of what are in it. Make sure you tick “show inverse relationship” as well so you can pick the entries in the field log. This would replace a “field name” field because the Link From Form field will take that role.

If you start with the link from the crop list, new records will have the crop pre-selected and you’ll have to select the field. If you start with the link from the field list, new records will have the field pre-selected and you’ll have to select the crop.

You keep your field log for your per planting information and shift any field specific information (name, location data, soil type, etc) to the field list. Then you can go from your field list to seeing all field log entries matching that field or you can go from the crop list to all field logs matching.

Hopefully some of that helps you out and gets you closer to where you need to be.

[Brendan]

Hi Stephanie,

I’m not sure I understand this bit:

I watched the video several times and when I click on the plus button in the “crop list” linking field, it doesn’t bring me to the “field log”.???

By clicking the + button, you’re adding a new record to the Field Log form which is then linked to your Crop List’s Link to Form field.

But as Sam suggested, perhaps you want to enable the Show inverse relationship option on your Field Log link to form field.

Hope that helps!

Brendan

[stephanie crocker]

Both of you, thanks for the help.

What I meant is that the behavior in the video is different than what happens in the current version. But I do see that this does create a new record in the linked form, and that instead of bringing me to the linked form, I am able to enter the data within the table in the current form.

Attachments:

You must be logged in to view attached files.

[stephanie crocker]

“If you start with the link from the crop list, new records will have the crop pre-selected and you’ll have to select the field. If you start with the link from the field list, new records will have the field pre-selected and you’ll have to select the crop.

You keep your field log for your per planting information and shift any field specific information (name, location data, soil type, etc) to the field list. Then you can go from your field list to seeing all field log entries matching that field or you can go from the crop list to all field logs matching.
”

can you clarify this a little? I totally understand the rest.

[Sam Moffatt]

What I meant is that the behavior in the video is different than what happens in the current version. But I do see that this does create a new record in the linked form, and that instead of bringing me to the linked form, I am able to enter the data within the table in the current form.

There are two add buttons: the first add button creates a new child record but leaves you in the currently selected record updating the table with it; the second add button (plus sign with the arrow) creates a new record and loads up the new record. You can still double click on the row number to open up the record as well.

If you start with the link from the crop list, new records will have the crop pre-selected and you’ll have to select the field. If you start with the link from the field list, new records will have the field pre-selected and you’ll have to select the crop.

Once you use either the second icon to create a new record or double click on the row number to load the record, clicking the “+” button in the top left to create new records will automatically link the parent record that you used to go into the child record.

You keep your field log for your per planting information and shift any field specific information (name, location data, soil type, etc) to the field list. Then you can go from your field list to seeing all field log entries matching that field or you can go from the crop list to all field logs matching.

So in the last approach you’ve got two forms, one that is your existing field log and a second one is the field listing. The field listing should contain the fields that is metadata about the field similar to what I presume you have in the crop list. If you’ve got some of that data in the field log, you should probably shift it over.

Hopefully that makes sense :)

[Author]

Posts