Tough One - Excluding Value List

[dtrots]

I have 2 tables and 1 join table.

Employees

Join Table

Vehicle

I have it set up so an entry in either Employees or Vehicle creates a new record in the join table so if you're in either table, the relationship will show up on either side. So I have a portal on Employees and Vehicles from the Join Table. In the Portals are the fields from the join table. These fields are drop down value lists. From fleet it looks at Employee name and from Employee it looks at Vehicle Description. When choosing one or the other makes shows up on each table. Everything works fine.

What I'm trying to do is exclude in the value list the vehicles or employees that have already been taken. The join table is storing a record of which employee has what vehicle but I can't for the life of me figure out a relationship where the value list will not include the employees or vehicles already stored in the join table.

Thanks in advance

[comment]

It's called a dwindling value list. This might be a good opportunity to update the method to v.8.5.

DwindlingVL.fp7.zip

[dtrots]

I'm starting to understand but I think your example requires the formula found only in 8.5? I'm using 8.0

[comment]

I see what you mean, but then the question is why do we need a join table at all.

If it's for history purposes, then this approach won't work at all, because it will eliminate every vehicle that has been ever assigned to an employee. Instead, we need a list of vehicles that don't have an 'open' record in the join table (and a mirrored arrangement for employees from the POV of Vehicles).

[dtrots]

I was originally going to try to have a button manually create a new record in the join table and at the same time mark a field in the employee or vehicle as "taken". This would eliminate them from the relationship. Then have a delete button erase the join table record and amrk them as "not taken".

I kept thinking to myself that the information is already in the join table, why can't we use that? I actually have the employee full name and vehicle decription being created in the join table. So I have it working that way and I was trying to make a relationship for the value list under "use this relationship" check box in the value list field choice dialog.

[comment]

I am not much in favor of duplicating information. But your problem is still not clear to me. If it's all right to "erase the join table record", then obviously you are not going to keep a history of past assignments. So I am asking again: why do we need a join table at all?

[dtrots]

Maybe I'm doing this right at all. I attached what I'm trying to do. You will see in fleet, there is a "Vehicle Assigned to" choice and when that employee is chosen, the vehicle description will also show in the employee table.

Once an employee is assigned a vehicle I don't want that employee showing up as a choice for another vehicle and once that vehicle is taken I don't want it showing for a choice for another employee.

I really apprciate the interest

Employee_vehicle_problem.fp7.zip

[comment]

You haven't answered my question. If it's true - as it seems - that (1) a vehicle can be assigned only to one employee, and/or (2) an employee can be assigned only one vehicle, and (3) you don't need to keep a history of past assignments, then you don't need a join table.

[dtrots]

Yes, one employee to a vehicle and one vehicle per employee. No I do not need a log and they can be deleted.

I'm not sure if I need a join table. I don't know how else to make the information show up on each table like I'm doing without one a join.

[dtrots]

Thanks!. I'll have to admit it was more complicated than I thought. I'm a novice working on a large project I have in my head learning as I go. I go to our local FMPUG meetings here in Chicago but we only meet once a month and this was eating at me.

If you need anything in Chicago I can return the favor, look me up.

Thanks again, Dave

[comment]

I would simply define a EmployeeID field in Vehicles and make the relationship:

Employees::EmployeeID = Vehicles::EmployeeID

Then place a field from Vehicles on a layout of Employees (no portal is required) and vice versa. That takes care of the relationships and the display. Now all that's left is the selection process.

It COULD be done with value lists, but as Fenton says, it would require a rather complicated structure of relationship - esp. since you want to make the selection available from both sides. A simpler solution, IMHO, would be to script the selection, and use a new window to present the available options.

For example, to select an employee:

Freeze window;

Open a new window;

Go to a list layout of Employees;

Find employees with no assigned vehicle (search the related Vehicles::EmployeeID field);

Pause script

The user selects the record by clicking on a button that resumes the script:

Set a variable to the selected EmployeeID;

Close the window;

Set the EmployeeID field to the variable;

Commit Records

This can be fancied up to no end, for instance by including a Cancel procedure, or preventing a new selection before clearing the existing one, and so on.

[dtrots]

Thanks, thta's not a bad idea either. It truely amzes me how many differant answers there are to a problem with this program.

Thanks again

[Kevin Wertz]

The dwindling value list works great until there are no values in the portal. Tried this in the sample file too and got the same result. Is there a fix for this?

[Søren Dyhr]

Yes there is:

Modify Fenton's:

_cVehicleIDs

Into this: ValueListItems (Get(FileName); "VehiclesUsed") & "¶~"

Provided the calc' is out of sight! Same trick needs to be done with:

_cEmployeeIDs ...

--sd

Edited December 7, 2007 by Guest

[Kevin Wertz]

Oh okay I was referring to Comment's dwindlingvl file that he posted early in this thread. I'm assuming he took a different approach because I did the fix you mentioned and it didn't fix it.

[Fenton]

Oops, forgot the tilde again :-! I deleted my earlier posts. It was the only way to remove the attached files.

[P.S. Comment's file has the ValueListItems() commented out (not a pun). By default it's using List(), and requires FileMaker 8.5. Look at the calculation. You can uncomment that line and comment out the one that says 8.5 (which will likely say "function missing" on FileMaker 8.]

Employees_Vehicles_Used.fp7.zip

Edited December 7, 2007 by Guest

[Søren Dyhr]

Ah! thats because you have upgraded to fm9, suddenly will non-equijoins only work with matching types on both sides of the relation, the simple fix is to change the OrganizationID into "text" instead.

--sd

[comment]

Filemaker, Inc. keeps changing rules on me every new version. Here's the updated file for version 9.

Explanation:

In version 9, a field used to match a Number field in a ≠ relationship is considered empty, unless it has an actual number in it.

DwindlingVL.fp7.zip

Edited December 7, 2007 by Guest

[Kevin Wertz]

Thanks guys, that fixed it. I had downloaded the dwindling value list sample file from Nightwing but Comment's is much simpler. Thanks!

[sujat]

Hi ,

I downloaded Employees_Vehicles_Used & DwindlingVL file . I tried to change it for use in my project but could not do it. Here is my problem -

I have a table named Cars where all the cars that is available for use are listed.

I have another table named Car Pool where cars from the table car which report to work for the day is listed.

The cars which report are selected by creating a new Rec and selecting a car from a dropdown list which has all the cars from the Table Car.There is no portal.

I want the the drop down list to be like your dwinding value list.

Thanks.

[comment]

The forum's attachments are not working right now. See if you can download my file from:

http://www.filedropper.com/dwindlingbydatefp7

[sujat]

Thanks Comment.

I downloaded the file. Can the list function be replaced by some other function ?

[comment]

Yes, ValueListItems - see the notes in the previous file.

[sujat]

I tried using the valuelist. One value List is showing all the values and the other showing none.

[comment]

In the DwindlingByDate file, if you define a value list "Today's IDs" to show values from field Today'sAssignments::ObjectID, show only related values starting from Assignments, then change the formula for the cToday'sIDs field to =

ValueListItems ( Get (FileName) ; "Today's IDs" ) & "¶ "

it should work in versions 7 and 8.

[sujat]

It is working perfectly.

Thank you very much.

[sujat]

I must be doing something wrong. Here is the file.

[sujat]

Hi Comment,

I changed the file name from DwindlingByDate to DwindlingByDate - v 8.0 and the value list didn't work. Today I changed it back to DwindlingByDate and it is working fine.

[comment]

That makes sense:

http://fmforums.com/forum/showtopic.php?tid/196076/

[StPeter]

I wanted to expand this dwindlingVL-solution with data from a second table, and show the combination in a portal ... without success.

Intention:

Have a portal with a collapsible employee-hierarchy, and all the sales-opportunities per employee. (i'm in a sales environment)

End-result should look like this:

Empl7__________183

__Empl1_________80

____Empl2_______70

________Opp1____50

________Opp8____20

____Empl3_______10

________Opp3____10

____Empl6________0

__Empl9________103

________Opp7___100

________Opp4_____3

The employee hierarchy is based on the dwindlingVL-example described before in this post, and is based on the table 'Empl'.

All the sales-opportunities-data (EmplId - Customer - OppDescription - OppValue - ...) is stored in the table 'Opp'.

If i base the portal on the 'Opp'-table, then i get all the opportunities, but not so that it fits with the empl-hierarchy (and additionally it will not show the 'roll-up' employees like Empl7 (who does not have his own opportunities as he is a manager).

If i base the portal on the 'empl'-table (=empl hierarchy) then i don't get the list of opportunities (the portal only shows the first opp for that empl, and on the same line, not underneath the 'empl-header'.

Is it even possible what i try to do here?

Any help appreciated!

rgds

[comment]

The employee hierarchy is based on the dwindlingVL-example described before in this post

I can't see the connection between the two.

In any case, a hierarchical portal is still a portal - and a portal can only show records from a SINGLE table occurrence. So unless you go into considerable lengths to create a combined table (which would need to be either calculated on-the-fly or scripted on demand), this cannot be done.