Calculation Fields in Drop-down Lists

[Karlos Fandango]

Hi.

 

I'm making a database for employees and as there are family members within the business, I want to break-up the names as follows...

 

1 - Prefix: [Mr Mrs Miss Ms]

2 - First_Name

3 - Second_Name

4 - Last_Name

 

Then use a calculation field to bring them all together as so - [Prefix & " " & First_Name & " " & Second_Name & " " & Last_Name]

 

Then when making new records for say something like 'Disciplinary Events'... wish to be able to choose an employee from a drop-down list. Obviously I do not want to use a values list for the drop-down made-up from either the First_Name or even the Last_name, as there are family employees within the Company with the same name. But when I try to use a value-list for the drop-down made-up from the 'calculation Field' mentioned... all I see is a question mark in the drop-down, which if chosen receive the message:

"This action can not be performed because this field is not modifiable".

 

I don't understand this as I've to my knowledge not denied the modification of the field in the layout... unless it assumes I am trying to do something to the calculation within the field which I am not... only trying to use the full name of an employee from a value-list made-up from the calculation field above.

 

This issue has forced me on occasion to abandon any notion of breaking-up names at all, and just use one field for the whole name which frustrating because when choosing from a values list, I have to scroll through more data than I'd prefer... but more, causes issues when using merge fields in letters to the employees because beginning a letter with:

 

"Dear Mr John Doe" rather than "Dear Mr Munford" is not always preferred.

 

Any comments would be appreciated, thanks. 

 

 

 

[weststreet]

Hi,

One solution would be to create a related table that contains first, middle, last names, and any other things like addresses and phone numbers. Create a calculation field called "first and last", and use your calculation there. Then in the main database, relate back to the "first and last" in value list "use values from field.." make the field in your main database a dropdown, and that will work.

Good Luck.

[eos]

"This action can not be performed because this field is not modifiable".

 

I don't understand this as I've to my knowledge not denied the modification of the field in the layout... unless it assumes I am trying to do something to the calculation within the field which I am not... only trying to use the full name of an employee from a value-list made-up from the calculation field above.

 

The problem isn't that you cannot use calculation fields in value lists – you can (if they're indexable) – but rather that you've applied the value list to a calculation field (you didn't say which field).

 

Your description is somewhat unclear, but assuming that Disciplinary Events is a child table for Employees, it should have an Employee foreign key – and that's the field you want to use the value list with.

 

See if the attached file helps you.

Disciplinary_eos.fmp12.zip

[Karlos Fandango]

...and that will work.

 

Good Luck.

 

Thanks Weststreet... I was starting to wonder if it was impossible and about to 'throw in the towel' as it were... but am now encouraged to solider on knowing it is!

[Karlos Fandango]

 

Your description is somewhat unclear...

 

Yes that may well be the case, I'm not only struggling with this issue but more importantly with the whole concept of relating data between tables. I seem to never know which table to put the 'foreign Key' in when relating two tables, is there a 'rule of thumb' or a question I should ask myself each time I relate tables? I get there in the end but just be trying different links.

 

Anyway, thanks for the file, I'l have a look in the morning... thanks.

[Mark Scott]

I seem to never know which table to put the 'foreign Key' in when relating two tables, is there a 'rule of thumb' or a question I should ask myself each time I relate tables?

 

Hi Karlos,

 

There is indeed a rule of thumb.  You want to put the foreign key in the "many" table: each record in that table will thus hold the value of the primary key from its single, related "parent" record.  For example, in the typical 1:M setting of an Invoice table and a Lineitem table—each invoice having one or more line items—each table will have, of course, a primary key* ("id") field that uniquely identifies the record within the respective table.  Then, the Lineitem table will also have a foreign key field ("id_Invoice") that holds the single id value of its parent invoice.

 

(FileMaker will let you do it the other way around, by placing a multivalued foreign key in the Invoice table, holding a return-delimited list of its related Line Items, but that is non-standard in the database world, harder to work with and maintain, and not advised under almost all usual circumstances.)

 

hth,

 

Mark

 

*BTW, if you're not already doing so, the best primary key is usually a field set to auto-enter, using the Get (UUID) function (prohibit modification, unique, not empty, etc.).  A slightly better twist for large databases might be to use an all-numeric version of UUID (via a custom function) and make the key fields number type rather than text, but in most cases the difference is not worth worrying about.

[Karlos Fandango]

Hi Karlos,

 

There is indeed a rule of thumb.  You want to put the foreign key in the "many" table: each record in that table will thus hold the value of the primary key from its single, related "parent" record.  For example, in the typical 1:M setting of an Invoice table and a Lineitem table—each invoice having one or more line items—each table will have, of course, a primary key* ("id") field that uniquely identifies the record within the respective table.  Then, the Lineitem table will also have a foreign key field ("id_Invoice") that holds the single id value of its parent invoice.

 

(FileMaker will let you do it the other way around, by placing a multivalued foreign key in the Invoice table, holding a return-delimited list of its related Line Items, but that is non-standard in the database world, harder to work with and maintain, and not advised under almost all usual circumstances.)

 

hth,

 

Mark

 

*BTW, if you're not already doing so, the best primary key is usually a field set to auto-enter, using the Get (UUID) function (prohibit modification, unique, not empty, etc.).  A slightly better twist for large databases might be to use an all-numeric version of UUID (via a custom function) and make the key fields number type rather than text, but in most cases the difference is not worth worrying about.

 

Thanks Mark... that is really helpful, I need to keep thinking "Child Parent" which you have reminded me of once more. I do set the Primary Key as a serial... but not  (prohibit modification, unique, not empty, etc) which I shall do in future.

"The foreign key in the many table" I shall keep that as my rule-of-thumb.

 

Once again... thank you.

[Karlos Fandango]

The problem isn't that you cannot use calculation fields in value lists – you can (if they're indexable) – but rather that you've applied the value list to a calculation field (you didn't say which field).

 

Your description is somewhat unclear, but assuming that Disciplinary Events is a child table for Employees, it should have an Employee foreign key – and that's the field you want to use the value list with.

 

See if the attached file helps you.

 

Thanks again for the file eos.

I went straight to look at your calculation field which drastically differed from mine...

 

Yours

 

Substitute ( 

 

  List (

    namePrefix ; 

    nameFirst ; 

    nameMiddle ; 

    nameLast 

  ) ;

 

  ¶; " "

)

 

Mine

 

namePrefix & " " & nameFirst & " " & nameMiddle & " " & nameLast 

 

Is mine unworkable in this scenario?

 

I at least understand that the 'Substitute' replaces the space " " with a carriage return, but if I need to use things such as 'List', 'Substitute' and anything else... where can I discover which each do? As I'm sure you know, in Excel you just click on a formula and you get an explanation... but if I'm going to use the build structure in FM... I'll need to at least have an idea what each formula does.

 

Thanks. 

[eos]

namePrefix & " " & nameFirst & " " & nameMiddle & " " & nameLast 

Is mine unworkable in this scenario?

 

No, yours would do fine – except that it doesn't check if a given field is empty before adding a space, which might result in an ungainly result. Since List() only lists non-empty fields, only those are used to build the list …

 

I at least understand that the 'Substitute' replaces the space " " with a carriage return

 

… where then each CR is substituted into a space (so be aware that it's the other way around!).

 

where can I discover which each do? As I'm sure you know, in Excel you just click on a formula and you get an explanation...

 

Actually, you want to know what functions do; formulas are what you write and (can) use them in … see here:

 

http://fmhelp.filemaker.com/fmphelp_12/en/html/help_func_cat.29.1.html#707771

 

 

Anyway, what about your value list? Did you get it to work, now having it applied to the correct field (in the correct table)?

[Karlos Fandango]

 

Anyway, what about your value list? Did you get it to work, now having it applied to the correct field (in the correct table)?

 

Yes I got it to work using your (Substitute, List method), I'd begun to think it was impossible, thanks for the help. Thanks also for the link... i'll take a look at it, it really does seem that a knowledge of these formula and scripts makes FM a whole lot more powerful.

 

• Maybe you could help me with another 'calculation field issue' or maybe I should open another topic?

 

One part of the database I'm dealing with, deals with 'Health and Safety'... I wanted a way of control and decided to generate a 'ticked [image below] in my database with its own unique reference [Primary Key]... I could then print one off for each employee... they would mark any items needing attention on the physical ticket [needs a helmet, first-aid box missing etc...] and return it to the office.

 

An operative would then go through the tickets and if any items were marked for attention, mark the same as "open" [an open issue] on the database Ticket. The physical tickets would then be filed away in numerical order.

 

Anyway, I wanted a way to mark the whole ticket [record] as "Open" if any item or items on the ticket were market as "open".

The reason for this is so I can add a 'dashboard' to the database which would have a Portal showing all "Open" Tickets needing our attention. Any item in a ticket could be either resolved by entering [drop-down list] "Closed" or preferable by adding a number from another physical "Resolve ticket", which would have hand-written on it the details of how the issue was resolved [invoice number item purchased, employee signature etc...] these could either be filed on their own or stapled to the ticket the employee flagged as a problem, only their number from the physical resolve would be added to an open issue field on a Ticket... they are only physical 'resolve tickets' not electronic.

 

OK, so my problem was... how to mark a whole ticket as "Open" if only one or more items on that ticket was flagged as "Open". The only way I could think of might be laughable to many...  but lacking the necessary skills and knowledge of FM... went for adding a single calculation field with the following calculation within it...

 

If ( Helmet = "Open" or Gloves = "Open" or DustMask = "Open" or EarDefenders = "Open" or Gloves = "Open" or WaterProofs = "Open" or HighVis = "Open" or SafetyFootwear = "Open" or DisposableOveralls = "Open" or FirstAidBox = "Open" or FireExtinguisher = "Open" or AdequateToilet = "Open" or AdequateWater = "Open" or AdequateWater = "Open" or ToolsWorkingOrder = "Open" or HeavyLiftingEquip = "Open" ; "Open" ; "Closed" )

 

I'm sure there must be a better way, though it does work.

Following is the image that might make sense out of what I have written...

 

https://drive.google.com/file/d/0B687aT8LAHKmc1kyVmEzMW9Rbnc/view?usp=sharing

 

Hope it makes sense!

[eos]

I'm sure there must be a better way, though it does work.

 

This is a structural issue; the better way is to add a table Issues as a child table to Tickets, where each record is category (Gloves, or Helmet etc.) with its own status (and other data, if required).

 

(Among other benefits, this would also allow you to report on individual categories.)

 

If you make Issues::status a number field, with an auto-enter value of 0, and set it to 1 to indicate the issue is closed – and use the same method for the Tickets status field – you could either make Tickets::status a calculation field

Sum ( Issues::status ) = Count ( Issues::primaryID ) // returns 1 if all closed, 0 otherwise

or a regular field and set it via a script that is triggered by a status change of a related issue.

 

If ( Helmet = "Open" or Gloves = "Open" or DustMask = "Open" or EarDefenders = "Open" or Gloves = "Open" or WaterProofs = "Open" or HighVis = "Open" or SafetyFootwear = "Open" or DisposableOveralls = "Open" or FirstAidBox = "Open" or FireExtinguisher = "Open" or AdequateToilet = "Open" or AdequateWater = "Open" or AdequateWater = "Open" or ToolsWorkingOrder = "Open" or HeavyLiftingEquip = "Open" ; "Open" ; "Closed" )

 

This data structure doesn't lend itself to an elegant solution, but you could use

Let (
  issuesList = List ( Helmet ; Gloves ; DustMask ; EarDefenders ; Gloves ; WaterProofs ; HighVis ; SafetyFootwear ; DisposableOveralls ; FirstAidBox ; FireExtinguisher ; AdequateToilet ; AdequateWater ; ToolsWorkingOrder ; HeavyLiftingEquip ) ;
  Case (
    PatternCount ( issuesList ; "Open" ) ;
    "Open" ; 
    "Closed" 
  )
)

[Karlos Fandango]

 

This is a structural issue....

 

 eos...

Thank you so much for taking the time to comment on this issue... it is late here in the UK so I'll have to try to understand just what you have written tomorrow. I know what I want, but have tried to accomplish it with the little knowledge that I have. My simple effort is working, but I knew that there must be a better solution out there as mine looks more like a hack than sounding databasing. Believe it or not I was even toying with the idea of having a table for each item, but soon realised the folly of such a notion.

 

At a fleeting glance, I 'think' I may have some difficulty understanding your instructions below...

 

"This is a structural issue; the better way is to add a table Issues as a child table to Tickets, where each record is category (Gloves, or Helmet etc.) with its own status (and other data, if required)."

 

...I'll have to see how I get on in the morning, though I do get the idea of adding a child table.

Maybe I'm stretching myself for a newbie, but again I know what I want and believe if I persevere I'll get there in the end. For I know through experience just how beneficial it is to have an administration structure in place that can not only take a heavy load, but offer information quickly.

 

As many today I write raw HTML5 and CSS3... which is quite basic but does tell of some degree of competence, yet even though FM doesn't really need a person to know any code at all, I have to admit that I am finding the concepts of databases difficult to fathom thus far!

 

Anyway, thanks once again... Karl.