I have been pulling my Hair out over this seems to be so easy Calculation HELP!

[WSaxton]

Not Sure if This is in the Right Area to Ask this

Alright Here is what i have , I have a Layout that includes to following items

1) Billing Type..................................... 2)Contract Amount...................................... 3) Billing total

Weekly--------------------------------------------A Number--------------------------------------------- Answer= Contract Amount / 52

Monthly ------------------------------------------A Number--------------------------------------------- Answer= Contract Amount / 12

Quarterly ----------------------------------------A Number--------------------------------------------- Answer= Contract Amount / 4

Semi - Anually-----------------------------------A Number--------------------------------------------- Answer= Contract Amount / 2

Yearly ---------------------------------------------A Number--------------------------------------------- Answer= Contract Amount

Where Billing Type Is a single Drop Down selection box , Contract amount is a single a user input , and currenly i have 5 fields set up for the answers where i just have set up a calculation for each answer field to take the contract amount number and devide it for the amount i need.

What i would like to do is go to a Single Billing type (Which i currently have useing a Custom Value list) , A Single Contract amout Box (which i have as user inputed) and then have a single Billing Total Box which its answer is determinied by a calculation that looks at the Billing method and the contract amount which would determine the approperate calculation and input it into the single Billing amount box.

Example

Billing Type = Weekly-------------------Contract amount = $5,200.00------------------Billing Total = $100.00

No matter what i try works , I would think it would be such a simple process but its driving me insane .

I Tried using a Nested Calculation But apperantly I have Absolutly no Good understanding on writing it cause it absolutly failed to work

HELP!!!

[LaRetta]

You can try this:

Contract Amount /

Case (

Billing Type = "Weekly" ; 52 ;

Billing Type = "Monthly" ; 12 ;

Billing Type = "Quarterly" ; 4 ;

Billing Type = "Semi-Annually" ; 2 ;

1

)

[Lee Smith]

Automatic message

This topic has been moved from "Database Schema & Business Logic → Custom Functions" to "Database Schema & Business Logic → Calculation Engine (Define Fields)".

[WSaxton]

LEE you are THE MAN SIR !!!!!!!!!!!!! That works awsome !!!!!!! Thank you

[Lee Smith]

Wow, thanks for the compliments, but I believe you meant them for LaRetta.

Lee

[WSaxton]

Yep I Did Sorry LaRetta got excited and miss spoke but THANK YOU TOO !!!!!

Alright now i got a good one for you both, the individuals I am making a data base for want visual easy ,I know I know , they want point and click so I have laid out a checkbox system for them to select what they want by the year all at once and they love, love love it since they are not to most savvy of computer users, but now i have no idea how to perform a search in such a way that i have a report based on month that includes the items in the month selected but not all months based on the current month. So for example my report would be run by month and show the following items for the current month and current month only out of all the records. If it wasn’t a check box display id would just run a report and have it sort to the current month, but not sure how to apply in this situation

Report one Display after search

Site Name...............Status.............hours...............Technician Type...................Technician to dispatch.

Then on a separate layout I need to display For the current Month

The Check box selections just in words for the Inspections selected for example the layout would have fields that look like this in March ,

March

Total Hours = 3.70

Jr Technician Only

Operating Inspection

Filter Change

Where As in June when i rerun the layout it would automatically update to say

June

Total Hours = 16

Jr Technician/Helper

Annual Inspection

Pre Cool

Evap Clean

Cond Clean

Belt Change

And So On and So Forth

I’ve thought about if functions, get functions and even tried applying the case function to turning the months selected into numbers then comparing the numbers to the current month to no avail.

I’ve attached a screen shot of the Layout for you to see what I mean, I need an Advil Bad, no a beer , ya a beer.

[LaRetta]

Hi WSaxton,

I believe that what you want is a conditional value list so it accumulates all of the selections into one list for the same month/year. Please see attached.

It is set up now to show all records but I left a search in the script (disabled) so you can see how to modify it. Note that it has grouped records by month using the cMonthDate calculation by turning all of your records into day 1 of that month/year.

Let us know if this is missing the mark. There are many people here willing to assist. :wink3:

CheckboxVL.zip

[WSaxton]

LaRetta Thank you so much I will check this in the morning and get back to you Im sure its perfect

[WSaxton]

LaRetta

I like what you did but I’m not sure how to make it fit my application I currently have . How would I get that information to auto fill out a print layout I have for the record. For example placeing a text box or a field in a layout so it automaticly enters that data? when the layout is pulled up based of the current calender month ?

[LaRetta]

I think I missed the mark here. When viewing the image again, I now see the months to the left as checkboxes? I already had a yellow flag when I saw the checkboxes but the months as checkboxes turns it into red flag.

I need to understand how you are structured. It is fairly clear that the Inspections are a single field in Customers? Is this correct? And are the months a regular text field in Customers as well? The image is difficult to tell. So you check January and June if the checked Inspections should take place in those months? Will you want a report where you total each Inspection type and list the Customers also, similar to:

Annual Inspection - total 3

Nancy Williams

Bill Brown

Mary Anderson

Pre Cool - total 2

Nancy Williams

Brenda Bailey

... if so, then you have a problem using checkbox field in Customers to hold your values. It is because Nancy Williams can't be in the body of the report twice (be in two different Customer records) since the report is generated in Customers. Your other problem is, if you check January and June for Annual Inspection and Pre Cool, what do you do when the Customer wants yearly Filter and Belt Change in October?

[LaRetta]

One further question ... do you have a table to track the service when it takes place, including service date, cost etc?

[WSaxton]

LaRetta Please Have a Beer and an Asprin prior to reading ;-)

All Good Questions , I am Looking to create two differant things useing this information, all check boxs are there own fields for example January has a field specified as Month January this follows true for all months , Same for Technician Type , each technican type has a field specified Technican Type 1 then technician type 2 and so forth Where all other fields for inspections have there own field for example AI for January is AIJan then AIFeb this follows true for all other check boxs based on the coulmn , Ya a lot of fields like i said they wanted visual easy point and click Now i need to work out a way to locate them. So for example i would like to run a report in the morning say tommrow for example that would show all records that have January checked as well as what items are due in january this would be based off the comptuer internal clock. So if it sees that this is febuary by the clock it would only populate a report showing the customers that have febuary checked and what is do , The Report Field in my minds idea would look some thing like this , i have a layout thats created that has it graphics in place already as well as the fields i need to populate as follows.

Site Name -------------------------- Status ----------------- Hours (This month not total ) -----------------------Technician Type --------------------------

Cliffs Flowers.............................Active .........................16.................................................................... Technician Only

Mikes Autoparts ........................Active ......................... 8 .................................................................... Technician / Helper

Lowes ............................. Active ......................... 36 ...................................................................Technican only

And so on and so forth as you can see the first report only uses a little bit of Data form the horrendious layout

The Second Thing i am trying to do with the Data seperatly from the above report , Im thinking that there may be a calculation where on a seperate layout that i have created that would be for the Technicians would show based once again on the system calender date the inspection items that are do for this month as shown below so for example as if i was to run the above report tommrow i already have a button in place that is on the right side of the technican field that apears for each entry that the above report would generate called dispatch , This button is a script that in the back round goes to a layout that is set up for the technicians showing location customer information and so forth pulled from the account records . I have an additional area at the bottom of the report that i need to populate with the inspection data so that is shows up on the form when its printed based on what is checked.

Operational inspection

Filter change

Evap cleaning

This would not show all items only the items that were checkmarked in the Account layout , Now lets say that next month I run the First report again then it would only show me the customers that have the month marked that matched the system clock , Now lets assume for a moment that Cliffs flowers has items scheduled for this month and next, So this month his account would show up on the report and when i printed his inspection form the items marked for this month would automatilly populate his form, Where next month if i ran the report he would obviously show up again since he has work do but the populated fields on his form would change automaticlly to indicate the items that were do then in contrast to the items due now

I hope i didnt confuse you , hell i confused my self for a minute but i read it again and its right .

******* i have a headache now

[LaRetta]

LOL, my head is pounding after the first paragraph!

You have boxed yourself into a dead-end. Always design with proper structure paramount, with the User experience and layouts secondary. With good structure, most things are possible. With bad structure, it will be much more difficult if not impossible. Even if things are easier for this ONE functionality, it will cost you in time and effort in everything else. And you WILL have to re-do much of what you've done once you realize this ... so cut your loses and change it now.

What you want IS possible with 3NF structure but I have no time today. Maybe others can assist otherwise I will try to come up with sample tonight or tomorrow. I do not like bringing bad news but I believe you already sensed you were in deep trouble. And the good news is ... most of your problems will disappear once this is corrected. :yep:

[WSaxton]

That would be be excellent if you can creat a sample , im going to have to do some research my self tonight into a 3nf structure i do appreceate your assitance :-)

[LaRetta]

Hi WSaxton,

I have put together a quick demo showing how I would approach the issue. There are other methods, using repeaters, possibly using filtered portals ... but this (I believe) is simplest. What you want is similar to a 'recurring' module for a calendar solution.

The first thing I would do is turn your grid sideways. When User selects month and then visually slides to the right to select the service, they can easily get off a line. Also, you will notice that your grid is wider than it is tall. With your format, you can never grow taller (past 12 months), only wider if you add more services. But if you reverse the grid then you can expand (and even print it) more easily. By using portal rows instead of columns, your solution can expand (more records just appear in the portal when a Service is added) instead of you having to drop into layout mode and add columns.

Note layout-level trigger ‘OnRecordLoad’ (which populates the Services table with the CustomerID) and also the individual value lists I created so your months can be properly spaced. Portal displays the Services table (table holding each service available and its rate) but the TechType and months selected are from the Maintenance table.

Now if you select the Report button, it will always find Maintenance records for the current month only. Those records could then be used to create your CustomerServices records for performing the actual work. I hope this gets you going.

ADDED: BTW, I did not include error trapping in that find script so you will want to add it. Note also that I have 'allow creation' checked on the Maintenance table in relationship from Services to Maintenance.

CheckboxVLREV.zip

Edited January 20, 2012 by LaRetta

[bruceR]

I find it interesting to note that when a checkbox is applied to a number field, the number field can contain a list of values; and a find operation will work correctly. CheckboxVLREVB.fp7.zip

[WSaxton]

LaRetta , WOW is all i can say WOW this makes completly more sense then what i was trying to accomplish prior you are amazing , :-) I see what you mean by turning it sideways it makes a lot more sense as well :-) Im gonna try this , ill let you know how it goes :-)

[LaRetta]

Hi Bruce,

LOL, it should be text so that it is viewable without entering the field. I originally planned one month per record (in Maintenance) but I decided that the selected months could be multiline in this 'recurring' table and translation could take place when the CustomerService record is created. There were many ways to go with this but I decided not to complicate the demo with more than necessary so I just stopped once I had the UI set up. I almost included a Mode field in Maintenance so User could specify whether Daily, Weekly etc... and then Mode could be included in the relationship and the dates could be calculated and ... well, I simply thought I should stop and let WSaxton work with base structure first.

As to why the find works ... here is my thinking but it can be wrong: FM allows text in a number field. And text in a number field CAN be found in search if it exists in the index (CTRL-I). And each unique 'word' exists in the index and carriage return is a word separator. But I find allowing non-numbers (in this case carriage return) in number fields bizarre in general. :)

[bruceR]

I agree that multilines in a number field is strange. I see that they do show up using the index (CTRL-I).

But a find does not work. To test, I changed your "12" value list item to "12 Dog Night" (one line; no quotes). Then selected it in a record. But I cannot find "Dog" or "Night", since they are not numbers, though I can find 12.

Anyway, thanks for whipping up the example, it is a great demo of the technique.

[LaRetta]

So it only sees numbers. Ctrl-I confirms it - text is displayed but only the number can be 'found'. Then this goes against the theory of the carriage return I guess except that FM always treats two lines as individual values. Thanks for pointing this out; it is interesting. This is the power and beauty of making mistakes. Some of my best discoveries have been when I mess up and get strange results.

WSaxton, remember to change the MonthNum in Maintenance to text. It will work as number but unless you increase the height of the field, you won't be able to view the multiple entries.

[WSaxton]

Hello LaRetta , well it took ten days at my skill level but i finally got the 3nf table working , YAY! Everything works perfect now , now I’m in a new world of lost , Possible could i pick your brain a tad bit more.

I have a couple of questions on security, I am using the filmmaker security and am NOT verifying from an external source , i have set all my custom privileges and they are exactly where i want them and work great. When one of my lower access users access a layout they are not supposed to the layout goes blank and reads <<no Access>..........how do i send them back to the previous layout they were in and is there a Database wide script for this or do i have to add something to each and every script I have created?

Also Digital Signing i have a field that is a drop down box with three choices, I want that box to auto enter a choice of the three which would be survey and prevent anyone but full access users from changing it and after the full access users change it to active i would like to lock the record from changes unless a full access user take the option back to its previous selection which would be survey

I’m almost there i tried but could not get find a solution to these problems I appeal to you again oh great LaRetta :-)

[LaRetta]

Nothing great about me (except maybe my ears, LOL) but I am pleased to try and help.

When one of my lower access users access a layout they are not supposed to the layout goes blank and reads <<no Access>..........how do i send them back to the previous layout they were in and is there a Database wide script for this or do i have to add something to each and every script I have created?

You might consider stopping them before they go. If Users have access to layout drop-down, they won't even see layouts with no access so I assume you mean stopping them via script. I would create a single script and call it as sub-script:

If ( Get ( AccountPrivilegeSetName ) = "User"

Show Custom Dialog [ "Managers only, sorry." ]

Else

Go To Layout [ restricted layout ]

End If

i have a field that is a drop down box with three choices, I want that box to auto enter a choice of the three which would be survey and prevent anyone but full access users from changing it and after the full access users change it to active i would like to lock the record from changes unless a full access user take the option back to its previous selection which would be survey

http://fmforums.com/forum/topic/82260-how-to-intercept-keystroke-from-value-list/page__fromsearch__1

This shows how to use a test (such as AccountPrivilegeSetName) on the field (when User makes selection in drop-down) and how to return it to its prior state if test fails.

[WSaxton]

would i add the sub script to every current script i have or ?

[LaRetta]

If you mean the layout switching then only attach it (as sub-script) to scripts which switch layouts. I do not know your solution enough to make specific recommendations but a single script with:

If ( Get ( AccountPrivilegeSetName ) = "User"

Show Custom Dialog [ "Managers only, sorry." ]

Exit Script

End If

... should suffice.

I agree that it is not an expected behavior. If a layout is <no access>, and it even disappears from Go To Layout drop-down so it cannot be manually selected then it should not even allow a switch to that layout. It even displays Layout <unknown> if User lacks privileges (in data viewer) so how can it jump to it (in theory)? So you cannot use script trigger attached to OnLayoutEnter because it fires after the original event (layout switch), as you know.

If you meant something else, let me know.