Ways to Add Data to a Join Table

[wmugrad28]

Hello,

I've been working on a database for a few months now (my first one with FM) and I'm starting to have trouble with something I thought I knew/understood. My confusion is with adding data to join tables. Since there seems to be so many ways to get data into a join I was wondering if someone could make a list of some of the different ways all while keeping a few things in mind.

1. Most of the "ones" in the relationship have a more than just 20 or 30 records. In an example of customers and equipment there might be hundreds of customers and thousands of equipment pieces. This makes me think that a value list is not always the best option.

2. For a number of things I'm working with I need at least four or five fields of data to make the right choice to add. Another reason value lists don't always seem to fit the bill the best.

What I have been doing most of the time works, but I'm not sure it is going to have problems down the road. For the customer/equipment example I have the Customer/CE/Equipment TOG and then I have a separate TOG for what I call a "viewer" TO related to a "temp" TO. If I am going to add a piece of equipment to a customer, I navigate to the layout of the customer I want to add equipment to and then click a button that runs a pretty complex script that copies all of the appropriate and available pieces of equipment to the temp TO. That information is then viewed in a portal with the viewer TO(with the Customer ID in a global field).

When a selection is made, a script is run to create a new record in the join table with a unique ID along the two IDs from Customer and Equipment. This has worked very well and allows me to do all kinds of filtering, but now that I'm more comfortable writing crazy long scripts, I would like to use RELATIONSHIPS to do more of the work for me as far as filtering goes. I do like the idea of using a "viewer", but what problems might I run into?

Thanks for any help! I'm still learning.

Greg

[wmugrad28]

Hello,

I've been working on a database for a few months now (my first one with FM) and I'm starting to have trouble with something I thought I knew/understood. My confusion is with adding data to join tables. Since there seems to be so many ways to get data into a join I was wondering if someone could make a list of some of the different ways all while keeping a few things in mind.

1. Most of the "ones" in the relationship have a more than just 20 or 30 records. In an example of customers and equipment there might be hundreds of customers and thousands of equipment pieces. This makes me think that a value list is not always the best option.

2. For a number of things I'm working with I need at least four or five fields of data to make the right choice to add. Another reason value lists don't always seem to fit the bill the best.

What I have been doing most of the time works, but I'm not sure it is going to have problems down the road. For the customer/equipment example I have the Customer/CE/Equipment TOG and then I have a separate TOG for what I call a "viewer" TO related to a "temp" TO. If I am going to add a piece of equipment to a customer, I navigate to the layout of the customer I want to add equipment to and then click a button that runs a pretty complex script that copies all of the appropriate and available pieces of equipment to the temp TO. That information is then viewed in a portal with the viewer TO(with the Customer ID in a global field).

When a selection is made, a script is run to create a new record in the join table with a unique ID along the two IDs from Customer and Equipment. This has worked very well and allows me to do all kinds of filtering, but now that I'm more comfortable writing crazy long scripts, I would like to use RELATIONSHIPS to do more of the work for me as far as filtering goes. I do like the idea of using a "viewer", but what problems might I run into?

Thanks for any help! I'm still learning.

Greg

[Ender]

There's no need to copy multiple fields to your viewer. Just set a global Status field to "Available", that will be a filter for a relationship to the Equipment table. You can filter by additional criteria if you wish. Then place fields on the viewer layout from the Equipment Available TO. When a piece of equipment is selected, grab the related Equipment ID and the current Company ID and create your new join record.

[Ender]

There's no need to copy multiple fields to your viewer. Just set a global Status field to "Available", that will be a filter for a relationship to the Equipment table. You can filter by additional criteria if you wish. Then place fields on the viewer layout from the Equipment Available TO. When a piece of equipment is selected, grab the related Equipment ID and the current Company ID and create your new join record.

[wmugrad28]

Clarification -> I copy the appropriate and available records from CE and paste to the "temp" TO. The "viewer" TO is just a related TO to the "temp" TO to see the available options.

There is something fundamental here that I'm not grasping. I don't understand what you wrote very well. It seems as though what I'm doing now is flexible and what you are suggesting is linear as far as filtering goes. I'm really trying to learn the right way... I'm not trying to suggest I'm right or doing it better (please don't take it that way).

What if you want to simultaneously filter by criteria other than information from the nearest TO in the TOG. It seems that you might only be able to filter from that nearest TO. Can you show me two basic examples of what you are describing? The two I would enjoy seeing are:

1. Adding a record to a join without filtering

2. Adding a record to a join with a filter or two.

It would be really helpful as I get bogged down in the lingo sometimes.

Greg

[wmugrad28]

Clarification -> I copy the appropriate and available records from CE and paste to the "temp" TO. The "viewer" TO is just a related TO to the "temp" TO to see the available options.

There is something fundamental here that I'm not grasping. I don't understand what you wrote very well. It seems as though what I'm doing now is flexible and what you are suggesting is linear as far as filtering goes. I'm really trying to learn the right way... I'm not trying to suggest I'm right or doing it better (please don't take it that way).

What if you want to simultaneously filter by criteria other than information from the nearest TO in the TOG. It seems that you might only be able to filter from that nearest TO. Can you show me two basic examples of what you are describing? The two I would enjoy seeing are:

1. Adding a record to a join without filtering

2. Adding a record to a join with a filter or two.

It would be really helpful as I get bogged down in the lingo sometimes.

Greg

[Ender]

There's no difference to the scripting you would use for adding records in a join table without filters vs adding records in a join table with filters. You just have more TOs to juggle on the relationship graph.

See the attached sample for one possible way to add join records in the situation you described (many records or many fields.)

This uses globals to temporarily hold the keys, which lets us see the related data (related Job record in my sample.) I'm using globals here so the new join record won't be created until the user hits Save.

JobsJoin-Table.fp7.zip

[wmugrad28]

Thanks Ender,

That makes a fair amount of sense. I think my scripting ability... will go down in the hall of fame. I had to come up with some scripts that were about two pages long to do some of this filtering.

I'm going to have a few questions after trying it out today. The first one that comes to mind is with globals. Do they make a pseudo one-to-many relationship?

Greg

[Ender]

I'm going to have a few questions after trying it out today. The first one that comes to mind is with globals. Do they make a pseudo one-to-many relationship?

Exactly. I'm using globals here for two reasons. First, there's no need to store these temp values in individual records in the parent table, since the process is ephemeral. Second, globals can be accessed from any table, without needing to know what record they are in. This second property helps when actually creating the new record from the join table.

[wmugrad28]

Some things makes sense, but I don't understand how to filter through relationships. I still have decided to use a viewer TO (but NO MORE temp TOs) to relate to Rental_Log. This TO (Rental_Log) is a join between Renters and CE (Customer Equipment). This allows me to view all the currently rented equipment from the viewer (there is a global on the viewer that limits the portal to only show records that have no End_Date). So far so good.

The problem is trying to figure out how to show the available rentals. Here are a few critera that I'm trying to filter all at once.

1. The record in CE must have the same name as the owner (from the C part of the CE)

2. The record in CE must have the status "holding".

3. The piece of equipment is not currently rented.

I have a REALLY difficult time with the third one. This means that if a record in Rental_Log has an End_Date then it needs to be filtered out of CE.

I can try and create a sample if needed.

Greg

[Ender]

Create another Status field in your CE table that gets set with a script when an item is rented or returned. Use this as the match field for the third filter.

Unfortunately, you can't simply use the End Date (or lack of) as a match key in CE because it does not reside in the CE table. While you can reference related fields in calcs, they cannot be indexed and so cannot be used as match fields in relationships.