Removing items from a value list?

[Kim R.]

I have an esixting database that I am working with trying tomake some changes to.

I have a table called "time sheets" that has a value list pop up menu, populated by a relationship to a "jobs table" job number field. Where each employee can choose a job number to track their time against. What I want to do is, once a job is complete, keep the record, but remove the job number from the pop up menu, so that there are not so many for each employee to have to scroll through to find the correct one.

Is there a way to do this without having to remove the record from the database?

Thanks..

Kim

[RalphL]

I post a sample file today that show how to do this.

http://www.fmforums.com/threads/showflat...true#Post165019

[Kim R.]

Thanks..but unfortunatly I don't quite understand....

Thank you though...

[LaRetta]

There may be an easy solution, Kim:

What determines when a job is complete? Example: If you have a Complete (number) field in Jobs which toggles 1/0 to signify that a job is complete (and a User clicks it, script sets it, or it changes because of other field data), then a new text field called JobsAvail (in Jobs table) can insert the JobNumber only if the job is still available (and remove it if not):

If(not Complete; JobNumber)

Set this JobsAvail field to be standard text, Auto-Enter calc, unclick 'Do Not Replace'. Then base your value list on this new field instead of your JobNumber field.

Of course you can use any calculation to determine whether JobsAvail should include the JobNumber (as long as it comes from within the same table), but I hope this gives you the idea.

You can also create a new table occurrence combining JobsNumber and JobsAvail but the above would be simplest, I believe. Much depends upon how job completion is determined.

If you want to go this route and have existing data, I'd be happy to explain how to kick-start the Auto-Enter to quickly bring it up to snuff. It's one of my favorite toys.

LaRetta

[Kim R.]

This sounds like exactly what I am trying to do......

I will be the one determining when a job is complete and basically I currently have a field set up that says Complete and I have been either just entereing yes or no, but that entery currently doesn't have any effect on the relationship between jobs and the time sheet field.

I could easily make that an Active Job Numbers field..but then I would just need instructions on how to toggle in on or off in the time sheet relationship.

thank you...You made my day but letting me know there is a way to do this!!

[LaRetta]

I've attached a very simple demo in which you may refer. Since I'm unaware of your skill level, I will be very specific ... I believe it is more important to give someone something they don't need than to assume and have you experience problems. This looks long (because I indicated every single key click you need to do) but it is VERY simple - just follow step by step and you'll have this whole thing done in 5 minutes MAX. You might want to print it for easy following.

We could just use your existing Complete field itself - but let's keep this clean and easy for you. Back up now in case you run into problems.

The data in your Complete field must be consistent. Use a value list. There is the possibility that the field has unwanted characters, both Yes & No or typos. But we will turn it into a checkbox toggle. If you don't have one yet, create a Custom Value List called Complete and only put a Yes in it. Attach this value list to your Complete field as checkbox. In this way, click inserts Yes and produces an 'x', unclick removes Yes and blanks the checkbox.

I will assume that if Yes exists it should take precident and that is all we will preserve. So to remove any existing No's, carriage returns or other messy stuff perform the following:

1) In Define Fields Jobs table, select your Complete field. Change type to calculation then click Change.

2) FM will tell you that it will change the data in the Complete field to whatever you put in your calculation. That is what we want. Click OK.

3) In calc box put: If( Position(Complete; "Yes"; 1; 1); "Yes" )

4) This will clean up your Complete field only leaving Yes if appropriate. At the bottom of the calc box, make calculation result TEXT. Then OK. Then OK again to exit Define Fields.

Now - with your Value List correct and your Complete data consistent, you're ready for the final piece:

1) Open Define Fields to Jobs. Select Complete field and change its data type to straight text. It will preserve the changes your calculation performed on the data.

2) Create a new field called ActiveJob. Make this field a CALCULATION. (yep, we're going to force update its field data as well).

3) Put this formula in the calc box: If(Complete <> "Yes"; JobNumber)

Note: The correct character of 'not equal' doesn't show here. Change the <> to the equal with a slash through it.

4) At the bottom of calc box, make it text. Say OK and OK again to exit Define Fields.

5) Go back into Define Fields ActiveJobs field. Turn it back into text and click Change.

6) On ActiveJobs select Options. From Auto-Enter tab, click Calculated Value. Calc box will open. Your formula will still be there. Say OK.

7) Right below that Calculated Value checkbox, UNCLICK 'Do Not Replace Existing Value.' Say OK and OK to exit Define Fields.

8) Now create your value list called Active Jobs (based upon values from field) and select your Jobs ActiveJobs field. Change your existing Jobs pop-up on your layout to use the Active Jobs value list instead.

You're done. In the demo, if you go to the Jobs layout and click-toggle your checkbox field, the active job will appear. Then from the TimeSheets layout, click the pop-up and take a look.

LaRetta

TimeSheets.zip

[RalphL]

Did you read the developer note? I would like to know how I could improve this demo to make it easier to understand.

[LaRetta]

Hi Ralph,

I'm unsure whether you are talking to me or Kim. I reviewed your file (and will be pulling more ideas from it over time). I always glean a great deal from your work!

And I wasn't meaning to step into the middle of this thread ... But there is much there that Kim doesn't need and, since she is new to Forums and by her initial response, I felt a very simple demo and detailed explanation might work best for her. I'm not at the FileMaker level you are but I've spent 30 years providing Procedural Manuals & User Training materials and I thought Kim could use a hand here.

I prefer that demos match exactly (or as close as possible) to their exact field names, structure and current setup. And I think anything NOT pertaining to the specific functionality in question should be eliminated so everything within a demo is clearly required (or at least adds support) to solving a problem. Your demo is for advanced Users, Ralph.

Far easier to add one field holding JobNumbers then basing your value list on it. Done! I could have changed her Complete field to hold the IDs and just rename it Available. But I didn't know how many places (on various forms etc) the label name may have been used (which might require manually changing) nor the field (which require script attachment). And changing a field from Complete to Available REVERSES it's meaning! It may affect many of her Users or reports. Or we could have created a new TO -- but however else I went the complexity increased or we might hit areas of potential conflict that I was unaware of.

Maybe it's some of my old 'prior 7' thinking but 'simple and lean' still reigns in my book. You can't view a demo more bare than mine. It has no additional relationships, no changes to her existing structure or process and only one added field (to hold the IDs).

I look forward to playing more with your demo and will be most happy to provide specific feedback when I get the chance. And please forgive me for stepping in to help her; it wasn't done in meanness or arrogance.

LaRetta

[RalphL]

No problem. You provided the help that was needed. I didn't have the time to prepare a new sample. My question was for K.Rogers, but I welcome feedback from anyone who is willing to give it.

[Kim R.]

I did read the note, but am such a novice at Filemaker, I just simply didn't understand the instructions...I havn't tried LaRetta's solution yet, as my DB is a work, but from reading it, it is what I need..simple step by step instructions.

I appreciate both of your feedbacks on this (for me complex) issue.

Thank you both!!

Kim

[Kim R.]

LaRetta,

THANK YOU THANK YOU THANK YOU!!! I just followed your instructions, and it works like a champ. Thank you so much, this is going to make my life so much easier.

Kim

[Kim R.]

I have one more question about my value lists...

As of now, in my timesheet table, the first field is the job number, and by chosing one from the pop up list, the 2nd fieled is filled in with the appropriate job description for that job number...I would like to reverse this, and let the user choose a job description, then it would auto fill in the appropriate job number...I have dug and dug but can't quite seem to find out how. Any help is greatly appreciated!!!

Thanks.

Kim

[-Queue-]

Here's how I do it.

Change the auto-entered calculation text field in your Jobs table to

If( Complete <> "Yes"; JobDescription & " - " & JobNumber )

and use this field for your value list.

Next, make sure the JobNumber field in your Timesheets table is listed first when you sort the fields by creation order. It is CRUCIAL that it be created before the JobDescription field in this table for the following technique to work correctly. If it is not listed before the JobDescription field, then duplicate the JobDescription field and rename the original one to OBS_JobDescription, or something similar. Then rename the copy to JobDescription.

Next, set the JobNumber field in Timesheets to be an auto-enter calculation, with 'do not replace existing value' deselected, of

RightWords( JobDescription; 1 )

and set JobDescription to be an auto-enter calc, with 'do not replace existing value' deselected, of

Let( R = RightWords( JobDescription; 1 );

If( GetAsNumber® = R;

Left( JobDescription; Position( JobDescription; " - "; 0; PatternCount( JobDescription; " - " ) ) - 1 );

JobDescription )

)

You can also add a validation to this field, if you are using a list instead of a menu, of

IsEmpty(JobDescription) or JobDescription = Jobs::JobDescription

where Jobs is a relationship from Timesheets::JobNumber to Jobs::JobNumber. This will ensure that the field can only be modified by selecting a value from the list.

What you will end up with is a value list of items that look like

JobDescription - JobNumber

When one of these is selected, Timesheets::JobNumber will retrieve the JobNumber from the selection and THEN Timesheets::JobDescription will remove everything to the left of " - ", and leave you with only the description in the field. This works because the JobNumber field's auto-enter calculation will occur before JobDescription's auto-enter calculation, IF the JobNumber field is created before JobDescription.

If you have any existing records that need to be updated, you will probably need to go through and manually reselect the JobDescription for each one.

If you had to duplicate JobDescription to get it in the right order, then you will need to then go through all field definitions, relationships, scripts, and layouts (including any buttons attached to Sort steps only) and change any references to OBS_JobDescription to use JobDescription instead. Once that is done, you can delete the OBS field.

[Kim R.]

Next, set the JobNumber field in Timesheets to be an auto-enter calculation, with 'do not replace existing value' deselected, of

RightWords( JobDescription; 1 )

and set JobDescription to be an auto-enter calc, with 'do not replace existing value' deselected, of

Let( R = RightWords( JobDescription; 1 );

If( GetAsNumber® = R;

Left( JobDescription; Position( JobDescription; " - "; 0; PatternCount( JobDescription; " - " ) ) - 1 );

JobDescription )

)

You lost me here...where do I do this step?

Thanks.

Kim

[-Queue-]

The first is done on Timesheets::JobNumber, the second on Timesheets::JobDescription. Both are changed in Define Database -> Fields.

[Kim R.]

I am not sure I did this right, but it didn't work...

I think I need something a bit simpler....Thank you for trying to help...

[comment]

Try here.

[-Queue-]

If you use Comment's technique, make sure you validate that JobDescription is unique or you may end up with the wrong JobNumber. Also, you may need to limit the number of characters in JobDescription, if users ever enter more than 100. My solution is good for when 'description' may not necessarily be unique or duplicates may be a requirement.

[Kim R.]

Ok..I have given up, I can't ma,e it work..I actually thought I totally messed up my back up file..but realized I have a mac pc format compatibility issue...I am going to post my file...maybe someone can take a look - and I found out that the file I have been working with is an editied version of Creative Pro JobTracker...

Anyway, on a PC, when I chose a job number on the timesheet table the job name is automatically placed in the 2nd field..on the mac, when I do that same thing, it stays blank, or eventually will fill in, but not in the correct manner...

Oh and just log in as guest..and you will have full access..

...and I thought this was going to be easy....ugh....

VPDJobs1.fp7.zip