List ( pfx & datefield1 & sfx ; pfx & Datefield2 & sfx ; pfx & datefield3 & sfx )
Will return the same return separated list and no plug in is required.
Awesome thanks! looks good so far... also, how about this other calculation I found that is holding us up? I am really not sure what that is trying to do...seems to be doing something fancy with the relationship, but I could be wrong. I am still trying to dig up that old documentation that i am sure came with this, but no luck so far :(Client Flow Sales Agent& " " &External("OzPf-SetPrefix"; Client Flow Sales Agent & " ") &External("OzDt-DatePrefs"; "FMDATE||0") &External("OzDt-FormatDate"; Client Flow Date In) &External("OzPf-SetPrefix"; "")Any help you can offer on that would be greatly appreciated. If I should create a new post for this let me know.
What does the output from this calculation look like?
I'd guess that it is returning a formatted date with a prefix of some sort so that it can be included in the text field. But I need to see what the results are supposed to look like before I can suggest a replacement.
Note that in your older version such functions and the return separated list of values in your first problem were needed for things that may not be required in current versions of Filemaker.
Hi there - I still have the old FM 7 kicking around luckily on Snow Leopard - I put that field in a layout and it shows as:Firstname Lastname 1Where Firstname Lastname is one of our sales agents.Also, to help myself get a better understanding (not sure if it helps or not) I broke down the first field used in this whole mess…basically there are 2 databases /tables at play here:Field Name;Sales Agent 1 Office1 Clients = this is to determine how many clients this sales agent had for the set time. Here is the calculation on that field:Sum(Sales Agent 1 Office1) - Sales Agent 1 Office1 NQWhere:Sales Agent 1 Office1 = this field is an Auto-Entered value in the Management database with the name of the sales agentandSales Agent 1 Office1 NQ = calculated field in the Management database with the following calculation: Sum((Sales Agent 1 Office1::Sales Agent Qualified)andSales Agent Qualified = calculated field in separate Office database with the following calculation: If(Office::Qualified = "No"; 1; 0)andQualified= Normal, Text field auto entered in the Office databaseThe relationship we have for Management=Sales Agent 1 Office1 is:Sales 1 Office1 Calc(Management table)=Sales Agent Match Key(Office table)That is where I found the Sales Agent Match Key is using that plugin here in the last update.I am still too new to FM to see exactly what witchcraft they were trying to accomplish when they initially wrote all of this reporting back in 2000, but yeah I bet it could very well be simplified in FM 12....you would laugh when you look at all these extra fields/tables they have to accomplish which should be easy :(Again, thanks for your help!
Firstname Lastname 1
So if you check the value in Client Flow Sales Agent for this record, do you see Firstname Lastname?
Any idea what the 1 means here?
I'd check this on more than one record and check what values are in Client Flow Sales Agent and Client Flow Date In. on each record as well
So yes, it looks like Client Flow Sales Agent is a list of our sales agents, and I have a layout with a dropdown which shows their firstname and lastname - i initially thought that "1" is to match up with the sales office...1 being SC, 2 being PC, etc...but, after checking a couple of records, here is what it showed:
For Sales Agent named "Bob Smith", Sales Agent Match Key showed as "Bob Smith"
For Sales Agent named just "Peter" with no last name, sales agent Match Key showed as "Peter 734362"
For the client flow Date In, when I run a find on that layout, i use this format for that field to search: 9/10/2013...10/10/2013 and that will return all of the sales for that sales agent in that time frame. When I put the Sales Agent Match Key on that layout, everytime I click Next Record, a new number showed for this Peter sales agent...example: 1st record showed as Peter 734362...2nd record showed as Peter 734366...3rd record showed as Peter 734372. Now the sticky part, I just tried with a Sales Agent who showed a first and last name, Hank Smith, and the match key showed up as Hank for all records? However all the rest of the sales agents showed as the example for Peter and Bob Smith as I would expect.
I hope I didnt make that too confusing...let me know if that helps
It raises more questions that answers. There's nothing here that would tell us why some cases return first and last name and other return only the first name.
The integer might just be a date expressed as a number.
since getasdate ( 734366 ) returns a date of 8/17/2011--which appears reasonable.
So far, it seems like:Client Flow Sales Agent & " " & GetAsNumber ( Client Flow Date In )produces the same result, but that expression would have also worked in your original file and does not explain the missing last names.
Yes - i will keep diggin into that...that new calc seems promising. Also, I checked another field it now almost seems as if my main problem could be relationships (perhaps something broke with the upgrade to 12, or needs something different) or something? Here is the scenario now, focused on a calculation that did not originally use that oAzium plugin:"
Calculating Volume for this Sales Agent in a layout in the Management db:
1) field for Sales Agent 1 SLC / Volume, and this is the field:
Sales Agent 1 SLC Volume
This field uses this calculation: Sum(Sales Agent 1 SLC::Member Sales Price)
Where Member Sales Price is in the other database named Office - I cannot get this to show anything, which makes me wonder if it is missing something to grab that data from the Office db/table.
The relationship I see is setup as Management=Sales Agent 1 SLC…the fields are setup as Sales Agent 1 SLC Calc(from the Management db = Sales Agent Match Key(from the Office db)
So perhaps something is broken there or maybe it is still just not returning anything because the relationship key used is not returning the expected data?
Thanks again - please excuse my lack of expertise in this area…still learning!
A key difference in how match fields match data for then vs. now is that spaces and punctuation characters were ignored with the indexing used back then and they are not ignored now so a difference of even one space character or a period could keep records from matching that originally matched. You'll need to examine the data in your match fields to see if that is a possibility.