Dr.L Posted November 16, 2011 Share Posted November 16, 2011 I'd like to have a conditional format have the ability for the same field to respond differently to two variables. So, if choice 'A' is selected in the field it would alter the fill color to red while if the condition is 'B' the same field would alter to fill in green...is there a clculation I'd use for this. I can easily format for one condition but multiples is very different... Thanks... Dr.L Link to comment Share on other sites More sharing options...
comment Posted November 16, 2011 Share Posted November 16, 2011 Apply both conditions to the field. Link to comment Share on other sites More sharing options...
Dr.L Posted November 17, 2011 Author Share Posted November 17, 2011 Understood, but how can I get each formula to pull up their own color. Is it an 'or' calculation. If so, HELP please... Thank you.... Dr.L Link to comment Share on other sites More sharing options...
LaRetta Posted November 17, 2011 Share Posted November 17, 2011 Conditional formatting treats each line as independent evaluations as in a Case() statement except, it evaluates on the last true condition and not the first, such as most calcs since v7. In your case, both conditions can not be true so they do not bump into (or offset) each other. You are looking for Boolean true so: First entry: Self = "A" ... and set the color to red add new second entry: Self = "B" ... and set the color to green And that would be like creating a calc of: Case ( Self = "A" ; "red" Self = "B" ; "green: ) Added ...if you only need two colors, do not forget you have the regular layout font color. You can make it green and then only run one test for red. Link to comment Share on other sites More sharing options...
bruceR Posted November 17, 2011 Share Posted November 17, 2011 Understood, but how can I get each formula to pull up their own color. Is it an 'or' calculation. If so, HELP please... Thank you.... Dr.L Like this. ConditionalFormat.fp7.zip Link to comment Share on other sites More sharing options...
comment Posted November 17, 2011 Share Posted November 17, 2011 it evaluates on the last true condition and not the first I prefer to think of it this way: it evaluates each condition in turn, and applies it if true. If both conditions are true, both are applied - but when they affect the same formatting parameter (such as color), the last one to be applied is the one that remains. Link to comment Share on other sites More sharing options...
LaRetta Posted November 17, 2011 Share Posted November 17, 2011 Thanks, Michael, that is an important distinction. Link to comment Share on other sites More sharing options...
Dr.L Posted November 17, 2011 Author Share Posted November 17, 2011 ...thanks all, but can I use this formula in the field for "Conditioning Formatting"? Or, am I better served to use it in the field definition? Link to comment Share on other sites More sharing options...
LaRetta Posted November 17, 2011 Share Posted November 17, 2011 If what you want is simply to change color then yes, conditional formatting is the way to go. In fact, conditional formatting is the way to go for MANY things which used to require creating calculations. You do not want to create a calculation in field definitions unless you really need it. Link to comment Share on other sites More sharing options...
Dr.L Posted November 18, 2011 Author Share Posted November 18, 2011 I am SO sorry, guess I'm missing something here as this is just not functioning now. I've gone under 'Format' and to 'Conditional Format'...placed in the given formula. How can I choose and select two various colors in the selection box after I enter the formula? There is choice for only one color at a time...what am I missing? The case function is not overriding the box selection and doesn't function without some selection it seems... Thanks... Dr.L Link to comment Share on other sites More sharing options...
bruceR Posted November 18, 2011 Share Posted November 18, 2011 I am SO sorry, guess I'm missing something here as this is just not functioning now. I've gone under 'Format' and to 'Conditional Format'...placed in the given formula. How can I choose and select two various colors in the selection box after I enter the formula? There is choice for only one color at a time...what am I missing? The case function is not overriding the box selection and doesn't function without some selection it seems... Thanks... Dr.L The example file shows exactly how to do this. Did you look at it? Link to comment Share on other sites More sharing options...
Dr.L Posted November 18, 2011 Author Share Posted November 18, 2011 Couldn't open it...I'm on a PC...keeps saying it's not modifiable and the acction cannot be performed... Link to comment Share on other sites More sharing options...
LaRetta Posted November 19, 2011 Share Posted November 19, 2011 The case function is not overriding the box selection and doesn't function without some selection it seems... I guess I confused the issue by giving the Case() comparison (my intent was to clarify only). All you put in the calculation value is Self = "A" on the first one you create and then Self = "B" on the second one you create. If you wish, I can create a demo but I think you'll get it if you take one step at a time and look again at what I said originally. Link to comment Share on other sites More sharing options...
Dr.L Posted November 19, 2011 Author Share Posted November 19, 2011 Nice...NOW I see it! Sorry, must have been a mind block for some reason! Perfect now...much thanks for "keeping it simple". The K.I.S.S. theory is much better sometimes. And, MUCh thanks for your patience and persistance...great help! Dr.L Link to comment Share on other sites More sharing options...
bruceR Posted November 20, 2011 Share Posted November 20, 2011 Couldn't open it...I'm on a PC...keeps saying it's not modifiable and the acction cannot be performed... Being on a PC should make no difference. But your response is ambiguous. We would need to start with basics: First of all, do you know how to download and unzip a file? Once unzipped, do you know how to find the resulting file? What exactly "keeps saying it is not modifiable?" What action are you trying to perform? Link to comment Share on other sites More sharing options...
Madwolfie Posted November 21, 2011 Share Posted November 21, 2011 Hi Just searched for this as I need to change the colour of a field when selected, but in the example file all that happens is the radio buttons change state when clicked on, the field does not change colour. I am using FMv11.0v2 on Mac OS X 10.6.8 Would be a great help to know how to change the colour of a field when it is clicked ?? thanks in advance Link to comment Share on other sites More sharing options...
hbrendel Posted November 21, 2011 Share Posted November 21, 2011 Madwolfie, this is a completely different question. You should have asked it in a new topic. But here is the answer: Suppose your fields are white, but when you select a field it should become yellow. In layout mode give all the fields a yellow fill. Select all the fields and go into the conditional formatting window. Enter "1" (without quotes) in the formula and select white as fill. HTH (update your profile, it says: Client 8.5) Link to comment Share on other sites More sharing options...
Madwolfie Posted November 21, 2011 Share Posted November 21, 2011 Oooops, just was the nearest thing when I searched, apologies and like the minimalist result. Thanks ! I did start out with the idea of getting the fields to change yellow AND producing a total JUST from those selected, but that seems a little too complicated for my brain (I am using a list view of invoice totals), sometimes we need to do temporary sums of several invoices for comparisons. Have updated my profile - didn't realise I had been away so long, moving house takes it out of you :-)) Cheers Link to comment Share on other sites More sharing options...
Madwolfie Posted November 21, 2011 Share Posted November 21, 2011 Ah, just noticed that your solution is not quite what I meant, as only ONE field changes colour - that is probably due to me not explaining properly, I need to have several fields (those in a temporary selection) change colour and ideally a total (in another field) added up from all those individual fields in the selection. Rather difficult to explain, but we need to make temporary comparisons on our Invoice listing, so we select a group of invoice totals in the Invoice List layout, and then we add them up on a calculator, we have to do this a lot over many different selections, so an automatic way would be great. I arrived at the changing colour idea as it would make adding them up easier - if we could see which ones were coloured ! Guess it is stretching the possibilities a little too far ? Thanks in advance Link to comment Share on other sites More sharing options...
comment Posted November 21, 2011 Share Posted November 21, 2011 I need to have several fields (those in a temporary selection) change colour and ideally a total (in another field) added up from all those individual fields in the selection. I don't think it's the same thing as: we select a group of invoice totals in the Invoice List layout, One speaks of several fields in the same record, the other of several records in a found set. What exactly is the nature of the selection? Must it be manual, or could it be automated by defining some criteria? Link to comment Share on other sites More sharing options...
Madwolfie Posted November 21, 2011 Share Posted November 21, 2011 Ok, obviously I am not making myself clear - apologies for that. In an Invoice List layout, which displays a list of all our invoices we need to add up several totals (the invoice total got from a 'total' field on a single invoice in a related table), This will be used as a comparison - this will be done several times on many different groups of selected invoices in this list - the results are for comparison purposes and are not needing to be stored or ever reproduced again. So, we manually select the chosen invoices and then add up the total that they make on a calculator, if the result is not what we wanted, then we have to choose a different selection - as I said this takes time and an auto way of doing it - just seemed like a neat idea. My brain cannot get passed this one. The easy solution is to make the selected invoices turn colour - that would at least make it easier for us to see which ones we are dealing with in a long list. But there would need to be more than one field coloured at a time. The perfect solution would be for a total to automatically appear (in a new field of course) from this selection, then when we deselect those and make a new selection those fields turn colour and the total appears in the new field. Maybe I am asking too much of Filemaker ?? Link to comment Share on other sites More sharing options...
comment Posted November 21, 2011 Share Posted November 21, 2011 So you want to be selecting records, then? Because everything points to that, except: But there would need to be more than one field coloured at a time. I think you mean the same field would be colored in several records, no? we manually select the chosen invoices and then add up the total that they make on a calculator, if the result is not what we wanted, then we have to choose a different selection That's what I asked about: instead of manual trial-and-error, couldn't you let the computer do this for you (whatever "this" is)? Maybe I am asking too much of Filemaker ?? I don't think so. However, this question needs to be about selecting before it can be about conditional formatting. Link to comment Share on other sites More sharing options...
Madwolfie Posted November 21, 2011 Share Posted November 21, 2011 I am sorry but I thought it was clear above - I will try again. On our Invoice List layout (which lists all the invoices we have, with fields 'cInvoice Total' which relate to an invoice TOTAL on a single invoice of one client). We need to be able to do a multiple selection on this list view and no, it is not possible for the computer to do it, as the criteria will change every time we make a selection it will be based on new criteria. At the moment it is not possible to select more than one field on a list layout - this is why I think it is beyond the possibilities. The conditional formatting was my way of getting around the above, by just getting the selected fields to change colour but even so you can only have one of the fields highlighted in this way - so it appears that I am trying to do something that is not possible. Having the database provide the total of all those selected fields, as well as changing the colour, would be wonderful. Link to comment Share on other sites More sharing options...
comment Posted November 21, 2011 Share Posted November 21, 2011 I am afraid it wasn't clear before and it's even less clear now. I asked (twice) if you mean selecting several records (i.e. several invoices). You keep speaking of selecting fields, but you have mentioned only one field: 'cInvoice Total'. I think you mean selecting records - but it seems impossible to get a confirmation or a denial from you. Link to comment Share on other sites More sharing options...
Madwolfie Posted November 21, 2011 Share Posted November 21, 2011 As the layout was a LIST view I thought it was self explanatory, the FIELD I want to select multiples of is the 'cInvoiceTotal' - and yes it will be a case of selecting multiples records on the List view. Link to comment Share on other sites More sharing options...
comment Posted November 21, 2011 Share Posted November 21, 2011 Thank you. This is a highly technical issue and proper terminology is critical to understanding. In a database, "field" means a column - so no, you won't be selecting multiples of the 'cInvoiceTotal' field; you will be selecting multiple records and - if you so prefer - the selected records (rows) will highlight the 'cInvoiceTotal' field. Now, Filemaker's native method of selecting records is to find them. To do this manually, you start by showing all records, then omit each record you want to include in your selection, and finally show omitted only. This is very simple, but not what you want in terms of user interface - so you'll need a more elaborate method and you'll need to build it yourself. The basic idea here is that records are selected by adding their ID to a return-separated list held in a global field or variable. The conditional formatting is then based on the condition = not IsEmpty ( FilterValues ( Invoices::InvoiceID ; $$selectedIDs ) ) Link to comment Share on other sites More sharing options...
Madwolfie Posted November 22, 2011 Share Posted November 22, 2011 Thanks, looks like we are getting somewhere. I will have a play and see if I can get it to work, I assume that the conditional formatting is applied to the field 'cInvoiceTotal' ?? And that in your formula 'FilterValues' is the global field with the list of IDs in. It isn't clear from the above (to me anyway) - I am a novice after all. Link to comment Share on other sites More sharing options...
Madwolfie Posted November 22, 2011 Share Posted November 22, 2011 Well I cannot get that to work, no surprise there. I tried conditional Formatting to all the fields I could think of, with no effect. My global field has two IDs in it separated by a return, I have set it up as text and as a container and neither of these work either. I admit I am probably missing something very basic but need a pointer ! Link to comment Share on other sites More sharing options...
comment Posted November 22, 2011 Share Posted November 22, 2011 (edited) I assume that the conditional formatting is applied to the field 'cInvoiceTotal' ?? The conditional formatting can be applied to any object in the body part of the layout. And that in your formula 'FilterValues' is the global field with the list of IDs in. No. FilterValues() is a Filemaker function. If you want to use a global field (I used a $$variable in my formula), name it gSelectedIDs and the formula will then become = not IsEmpty ( FilterValues ( Invoices::InvoiceID ; Invoices::gSelectedIDs ) ) The global field must be a Text field. --- P.S. I hope you understand you will need a button for selecting/deselecting a record, attached to a script that adds/removes the record's ID to/from the list. That's what I meant earlier when I said this is about selecting before it can be about conditional formatting. Edited November 22, 2011 by comment Link to comment Share on other sites More sharing options...
Madwolfie Posted November 22, 2011 Share Posted November 22, 2011 P.S. I hope you understand you will need a button for selecting/deselecting a record, attached to a script that adds/removes the record's ID to/from the list. That's what I meant earlier when I said this is about selecting before it can be about conditional formatting. Cool! Thanks, that is all clear and now working - I did get the button bit, had already worked on it beforehand. Now it just leaves the calculating of the total of the selected Invoices, so shall see if I can manage that. This will save my wife a huge amount of fiddling around when we do the accounts and looks like being a very neat solution to the problem. Thanks - I mostly despair of forums but have to say FMForum has always come up trumps for me, it may take a while getting the exact idea of what is required, but some very neat tweaks exist in my databases courtesy of you guys. Link to comment Share on other sites More sharing options...
comment Posted November 22, 2011 Share Posted November 22, 2011 Now it just leaves the calculating of the total of the selected Invoices That's actually the easy part here: if you are using a global field, you can define a self-join relationship as: Invoices::gSelectedIDs = Invoices 2::InvoiceID and add a calculation field = Sum ( Invoices 2::Amount ) Or, if using a variable, you can have the same script add/subtract the current record's amount from the total held in a second variable. Link to comment Share on other sites More sharing options...
Madwolfie Posted November 22, 2011 Share Posted November 22, 2011 WOW ! That is going to take some thinking on my part, must get back to painting at the moment. I should really be doing this in the evening as it is not how I earn a living . . . fine art is my career :-)) Now you understand why maths, calculations etc requires more effort on my part ! So there will be a delay until I can get back to this, but I think I understand the principle of the above Thanks again for a simple, eloquent solution. Link to comment Share on other sites More sharing options...
Madwolfie Posted November 22, 2011 Share Posted November 22, 2011 Well, it was just too much temptation, I enjoy trying to get things to work, so I am back at it now . . . Just clarify what is 'Invoices 2' - is it a new field ? And you say add a self-join relationship, to what do I add that to ? The rest I think I can handle Link to comment Share on other sites More sharing options...
comment Posted November 22, 2011 Share Posted November 22, 2011 A self-join relationship is a relationship between a table and (another occurrence of) itself - see: http://www.filemaker.com/11help/html/relational.11.10.html#1028163 When you add another occurrence of Invoices to the relationship graph, Filemaker will initially name it "Invoices 2". You can then rename it to whatever you wish, e.g. "SelectedInvoices". Link to comment Share on other sites More sharing options...
Madwolfie Posted November 22, 2011 Share Posted November 22, 2011 That is what occurred to me initially and yes, I have got it to work - absolutely amazing - thank you so much Very cool, Jo will be pleased at the amount of time this will save her THANKS! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4548 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now