Value List help needed

[Corey-Troy]

I am trying to reduce the potential size of a Value List, that i use on a layout.

I posted a previous thread about sorting, but think if possible, i would be better off removing no longer required values from the list.

Currently the value list is a Simple: ALL Records from another field in another table.

How can i use a calculated field to make up the Value List instead?

When i tried it, i get a Cannot be Indexed alert, and i thus get not values populated in the Value List.

I am trying to use a Cal field that states:

Case(Job Card Processed ≠ "Job Costed";Job Number;"")

This way if the Job has been costed, the Job No will not be listed in the value list, but i cannot seem to get it to work.

Corey....

[Corey-Troy]

I managed to change the calculated field to a Auto Enter Calculation, so that it can be Indexed, but i still get no list of Values.

[Vaughan]

My guess is that the Job Card Processed field is related, forcing the calculation to be unstored.

[Corey-Troy]

Yes, Is there a way around that?

Is there a way to create a Dynamic Value List some how?

[Corey-Troy]

Thinking out load:

Would something like adding a separate table with a field that copies the value from the other field work, if i create the other tables field as the value list, and simply delete(via script) the ones used ?

[comment]

You're not giving us the full picture.

[Corey-Troy]

Full Picture:

I have a Table called Job Cards with among others, a field called Job Number(Unique Job Number).

I also have a Job Costed Text field, that displays "Job Costed" IF the Job Number is displayed in a field in the Costing Table.

I have another table called Costings, that has a field that i want to list the available Job Numbers from the Job Cards Table.

But i do not want in time a mass of Job Card numbers listed in the Value List.

I want ONLY the Job Numbers that DO NOT have "Job Costed" as the value in the Job Card Table.

This way if a Job has been costed, then the Job Number is no longer displayed in the list of Job numbers.

I googled this and found a NightWing example file, that does precisely this via a script, but i cannot modify it to suit as i do not understand the steps all that well(beyond my ability).

[comment]

Do you mean you select a job for each costing, and you want to select from jobs you haven't yet selected for another costing?

[Corey-Troy]

Each Costing Sheet, will be assigned to a Job Card Number(between 1 and 15 Job numbers).

I have now created another Table, that when a New Job Card is created, it creates a New Record and pastes the Job Number into a field in that table too.

I set the Costing Sheet Job Number field Value List to look at the new Table field, instead of the Job Card field.

I created a script associated to a button, that now deletes a Record found in the new table, so that, when a Job number is selected, the Job Number is then deleted(record) so that it does not display in the value list anymore.

Pretty crude, but it sort of works, but it needs tweaking now.

The script looks like this:

Copy[select;Costing Sheet::Job No]

Go To Layout["Costed Job Cards"(Costed Job Cards)]

Enter Find Mode[]

Paste[select;Costed Job Cards::Uncosted Job Numbers]

Set Error capture [on]

Perform Find []

Delete Record/Request [No Dialog]

Sort Record [Restore;No Dialog]

1). How can i set the following script to simply exit the script, if there is No records found in the new table?

and

2). How can i get the script to include all Job numbers that have been selected in the Costing Sheet Job number field(Repeating field-15)?

Edited December 16, 2009 by Guest

[comment]

I am sorry, you move too fast for me - I am still trying to digest the "real picture".

Each Costing Sheet, will be assigned to a Job Card Number(between 1 and 15 Job numbers).

I have no idea what that means. I think I understand assigning a Costing Sheet to a Job Card Number - but that would be ONE Job Card Number. Where do the other 14 numbers figure in this?

[Corey-Troy]

There are instances when a Job is broken up into more than one Job Number, for instance:

Job 1234 - Supply Product

Job 1235 - Install Product

Job 1236 - Service Product

When it comes time to Cost the job, all related Jobs can be Costed onto the one Costing sheet.

I have 15 Job Number (repeating)fields on the costing sheet, but it would be rare(but feasible) that 15 would be required.

Hope that helps complete the picture.

I apologize for the jumbled explanation, it is sorted in my head, but sometimes putting it on paper can be not as sorted.

[Vaughan]

I have 15 Job Number (repeating)fields on the costing sheet...

Bingo. Repeating fields.

[comment]

Well, that's one issue (though I am not sure it is the cause of the problem being discussed). My feeling is that you should step back and re-evaluate your entire data model. But I could be wrong, since you have told us nothing about the real-life situation that this data model needs to reflect.

[Corey-Troy]

If i get rid of the Repeating field to display a list of Job numbers, i would have to create 15 separate fields, then that would make a calculation needed to go through each of the 15 fields wouldn't it?

[comment]

No, you would use a join table - see a demo here:

http://www.fmforums.com/forum/showpost.php?post/246136/

Once you have that figured out, perhaps you should also take a look at this - it may be similar to what you were looking for to begin with:

http://fmforums.com/forum/showpost.php?post/274656/

[Corey-Troy]

http://fmforums.com/forum/showpost.php?post/274656/

is what i was looking for.

But it is beyond my Filemaker knowledge to get that to work in my db instance.

Currently as is, i can get what i have to work perfectly, so long as only a Job Number is entered into [Costing Sheet Job::rep;1].

My script looks like this:

If[(IsValid(GetRepetition(Costing Sheets :Job No;1)]

Copy[select;Costing Sheet::Job No] (Repetition 1)

Go To Layout["Costed Job Cards"(Costed Job Cards)]

Enter Find Mode[]

Paste[select;Costed Job Cards::Uncosted Job Numbers]

Set Error capture [on]

Perform Find []

Delete Record/Request [No Dialog]

Sort Record [Restore;No Dialog]

If i add the same steps again, but refer to Repetition 2, only the 1st Repetition Uncosted Job record is deleted, not the 2nd.

[comment]

Sorry, I can't help you with that, because (a) I don't know the context in which the script is running, and ( I don't think that's the way to progress.

The concept of a join table is not VERY difficult, but it might take longer than an hour - the same, even more so, applies to dwindling value lists.

[Corey-Troy]

Got it Ok now.

I managed to extend the script i was using to work with each of the repeating field Job numbers.

{I know most, if not all fmp deleopers seem to hate Repeating field, but i like them}

I just need to create a script now, to make it fool proof, in the instance the user selects a Job number, processes it via the script[delete record], then changes his/her mind and removes it, to add the Job number back in as a new record again.

Edited December 17, 2009 by Guest

[comment]

I know most, if not all fmp deleopers seem to hate Repeating field, but i like them

You are wrong about that: the smart developers love repeating fields - but only where they are appropriate.

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