Import record info from one table into another as a drop down list

[tbyrd1969]

I am having a problem doing this in FMP 8.5. I have a table with all of my jobs (as separate records). I want to create a time sheet table for employees to log their job functions in. I need to have the time sheet table be able to show only the live jobs (after a find is performed) and have them show up in a pull-down field for the employees to choose from. If anyone has any advice, I would really appreciate it. If you need some additional information, please ask!

 

[JeanW]

If you give a status to your jobs (like 'Active'), and make a relationship between your tables.
Based on this relationship you can make a valuelist to show only the 'Active' jobs.

 

[tbyrd1969]

Thanks for the reply! I think I am half way there already, but can't figure out the rest. I currently have a field called "LiveCompleteBilled" which uses a value list with each option. So all jobs are tagged as one of those three. So how do I get a valuelist field (or function) in the other table to show only the live jobs? I can't figure it out through the FMP Help......

 

[JeanW]

It is all in the relationships.

In table A you have your jobs and the flagField LiveCompleteBilled.

In table B you want a dropdownlist with only the 'Live' tagged records from table A.

A possible way to go:
Make an autoEnter field in table B (liveTag) with the text 'Live'.
If you have already records in table B, you populate this field with 'Live'.

Make a relationship between your LiveCompleteBilled field from table A and liveTag in table B.

Make a valuelist 'LiveJobs', use values from field, use value from first field, specify your relationship, include only related values.

What is does:
In table B you have a fixed value for 'Live'.(with the autoEnter all new records will have that value)
In table A LiveCompleteBilled can have a value 'Live'.

You have a relationship that resolve to thrue when 'Live' in A = 'Live' in B.

When it is, your valuelist will resolve to thrue also and will show only related values.
Related values = the values of records where 'Live' in table A = 'Live' in table B.

There are several other ways to achieve what you want, but I believe this is the most basic so you understand the way of working.
From this point on you can start trying things out.

 

[tbyrd1969]

OK - I am following you up until:

"Make a valuelist 'LiveJobs', use values from field, use value from first field, specify your relationship, include only related values."

Would it be possible to expound on this a little bit more? I am not understanding what you are specifying. What do you mean by A)use values from first field (which field would that be?); and B) FMP asks "Include related values starting from:" (which table should this be?)

Thanks again - I REALLY appreciate your help!

 

[JeanW]

Make a valuelist 'LiveJobs', use values from field, use value from first field, specify your relationship, include only related values."

Those are the FileMaker labels you will see when you create your valuelist.
To reach that: File - Define - valuelist
If you are on FM 8/8.5

The name of the relationship is the one you just made, which you can find in the drop down list that FM will show.

 

[tbyrd1969]

Sorry to be a pain -

I understand how to make the relationship, but can't get the correct records to show in the value list. I am getting confused when I need to define the value list. It asks me to "Use values from fieldSpecify Field)" I cannot figure out which field to choose there.

It then also asks me to choose A)Include all values or B)Include only related values starting fromIt then asks for a table, and I don't know which one to choose).

If you can provide ANY assistance, I would really appreciate it. Thanks again for your time!

 

[JeanW]

Let's go a few steps back....

What you want:
...to be able to show only the live jobs (after a find is performed) and have them show up in a pull-down field...

What you have:
... "LiveCompleteBilled" which uses a value list with each option. So all jobs are tagged as one of those three.

So the status of a record can be or 'Live', or 'Complete', or 'Billed', but we only want the records with status 'Live'.

With other words, you want a drop down list with all the record where the value in field "LiveCompleteBilled" is 'Live'.

Correct so far ?

You have 2 tables, one with the jobs and one timeTable.

You want the drop down list with only the live jobs in the timeTable so users can choose a 'live' job from the list.

You make in the timeTable an autoEnter text field, 'liveTag', with the value 'Live'.
This way you will always have the value 'Live' in that field the moment you create a new record.

For the existing record you have to make sure (by hand) that all the records have the value 'Live' in that given field.
Now we are sure all the records in the timeTable will have a field with the value 'Live'.

In the jobtable, only the records where the user made a choice for 'Live' in the "LiveCompleteBilled" field, will have the value 'Live'.

You make a relationship between your timeTable and your jobtable, call it f.i. Jobs, where 'liveTag' = "LiveCompleteBilled".

With other words, when in field "LiveCompleteBilled", in the jobs table, the value is 'Live', this will be equal to the fixed value in the 'liveTag' field in the timeTable.
So your relationship resolve to true.

You want a list of jobs where the "LiveCompleteBilled" value is 'Live'.
The only records from your jobTable where that is true is for the records with 'Live' in the "LiveCompleteBilled" field.

So we need a value list for all the records where that is true, and the only moment when that is true is when 'Live' is in the "LiveCompleteBilled" and you only want to see that in you timeTable.
The only moment when taht is possible is when the relationship between those two table resolve to true.
So your value list needs to be based on....yes your relationship. And only when 'Live' in timeTable = 'Live' in jobTable.

You make a valuelist:
"Use values from fieldSpecify Field)"
The field based on your relationship, in your jobtable.
Which field ?
The field that holds the values you want to show your user in the drop down list.
And that is ? The field with the name of the jobs.
But you want only the records with the Live jobs, right ?
Well, you will have them, because you only take the records where the relationship resolve to true and those are the records where timeTable::liveTag = jobTable::"LiveCompleteBilled".

Include only related values ?
Try it out. With and without and see the result.

If stuck. Post again.

HTH