Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Usage Calculation


xtrim

Recommended Posts

Hi

 

I have the Following fields :

 

Unit_id

Status

 

Status is calculated value that have sevral results : Busy,Available,Permanent

 

I need to calculate the usage of each unit_id I have by the following rules :

 

If Status="busy" or "permanent" -> 100% usage for current date

If status="available" -> 0% usage for curren date

 

The calculation should calculate the usage from the day the record was created till current date.

I do not want to use another table/portal.

 

I already got a headache from trying to figure that out... blush.gif

Maybe someone got a cure for me?

Link to comment
Share on other sites

Try this calculation with a result of date:

 

Case(Status="busy" ,Status(CurrentDate),

Status="permanent", Status(CurrentDate),

Status="available",TextToDate("")

)

 

HTH

 

Lee

 

smile.gif

Link to comment
Share on other sites

It's not what I need.

 

The final rsult should be for example "75%".

The caculation each day should add 0 or 100 to the total sum and divide it by the total days passed since the record was added.

 

Therefor your calculation does not answer my question, but thx for trying smile.gif

 

P.s

 

I don't think it can be that easy....

Link to comment
Share on other sites

I see I made two mistakes in responding to your question.

 

1). Trying to answer the question based on the limited and confusing information,

 

2). Not starting my reply with "If I'm understanding you correctly, and I'm not sure that I am, then you could TRY a calculation such as this."

 

> It's not what I need.

 

That's for sure.

 

> The final result should be for example "75%".

> The caculation each day should add 0 or 100 to the total sum and divide it by

> the total days passed since the record was added.

 

By this logic, each new day would be 100% of the usage.

 

>> Status="busy" or "permanent" -> 100% usage for current date

>> If status="available" -> 0% usage for curren date

 

You said that it would be 100% or zero depending on the choice of the Result Field.

 

> Therefor your calculation does not answer my question, but thx for trying

> P.s

 

You're welcome.

 

> I don't think it can be that easy....

 

Oh but it is, I just made a sample file of what you described in your follow up post, and it was easy. You should play with it a while as it will be a good exercise for you. All you have to do is think about how the percentage should be calculated and from what source.

 

Hint, you are going to need a few more fields then you disclosed in your post (i.e. projected end date and a couple of elapsed times).

 

Good Luck,

 

Lee

tongue.gif

Link to comment
Share on other sites

Originally posted by xtrim:

[qb]If Status="busy" or "permanent" -> 100% usage for current date. If status="available" -> 0% usage for current date

 

The calculation should calculate the usage from the day the record was created till current date.

I do not want to use another table/portal.

[/qb]

To do this, you will need to log the status as it changes. This would be done in a related file.

 

If you think about doing it manually, just looking at the current status of a record will not allow you to calculate the history. You need to know when it changed from "in use" to "available" and back again. You would have had to have been recording this since the record was created.

 

I suppose you could do it with one file but you would have to run a script everyday without fail (and only once per day) to update the calculation result. That would be a very delicate structure IMHO.

Link to comment
Share on other sites

Hi XtriM,

I can offer you a cure, as I've worked on various systems that have done something like this, sometimes using a related table and sometimes not.

 

If you choose not to use a related file, then the stored history of unit status will be limited by FileMaker's maximum field size. However if you store single letter status indicators (eg 'A' for Available, 'B' for busy and 'P' for permanent) then you'll be able to keep tracking for around 87 years before you run out of space, and that should suffice, I'd guess. wink.gif

 

If you store more than character per day, then the maximum will be reached in proporationaslly less time, but even storing nine character status history indicators (eg 'Available', 'Permanent' etc), it would be fifteen years or more before the field would max out at 64k.

 

A further issue that needs to be considered is how best to ensure that the current status is captured for each day. The simplest way to do this may be by the use of the 'Today' function, which automatically recalculates each time the file is opened. For most purposes, I avoid the use of 'Today', because in large files it can slow down file opening (especially if there are a lot of dependent calcs), and on server implementations that run 24/7 recalculation has to be prompted, since the file is not reopened daily. However if these condiderations don't apply in your case, then the 'Today function may be an ideal solution. If not, then a script to update the history will be required, and I would recommend that you then use a plug-in to automate the triggering (eg Activator, SCRIPTit, Events etc).

 

Finally, regardless of whether you choose to use the 'Today' function or a script to prompt updates of the unit status history for each record, you will still have to account for the situation where there may be some days that the file will not be closed/reopened. The method you choose will therefore have to account for that in some way. For example, if the file is closed on a Monday and not reopened until Wednesday (or if you are using the 'Today' function and the file is kept open on a server all that time), your calculation will have to impute a status for Tuesday. If the status for Monday and Wednesday are the same, then it may be reasonable to impute that the status for Tuesday will be the same also, however if they are different, you'll have to define a 'logic' for deciding which status should be used for Tuesday.

 

As an example of a system that will track statuses automatically and calculate usage based on the status history of each record, I've stripped some calcs out and placed them in a small file for you to look at. The file is available at:

 

http://www.nightwing.com.au/FileMaker/demos/Usage.zip

 

The demo requires FMPv5.5 or above, as it utilizes the GetField( ) function. It incorporates an automated process using the 'Today' function. Although you are needing to track a calculated status, the demo uses a radio button input field for status - but the technique will work just as effectively either way.

 

What the demo does is automatically records the closing unit status for each record for each day the file is opened. If several days have elapsed since the file was last opened, it records the immediately preceding status for those days.

 

If you require a different logic in the way unspecified days are handled (eg a default for days when the file is not opened, or automatic backdating of the most recent status to the last specified status in history) then slight changes of the code could be made to achieve that, using the same basic (and not so basic...) principles. cool.gif

 

The set-up for functionality of this kind is a little tricky, so you'll need to go over the calcs in the related file with an eye for the detail and the driving logic, before you map out an implementation of your own. wink.gif

Link to comment
Share on other sites

Thanks you all for helping out.

 

I managed to do it with a realated record (before I saw Rays' solution..) and it is working fine. It basically depands on Last_modification_date.

Lots of realationships cause it uses about 10 files....

The App is (should smile.gif ) run 24hrs a day except weekends and holidays but it's running anyway.

Anyway, I don't have any plugin and I can't count on running a scrip daily cause of the limited web features that the web companion has to offer.

 

At first I tried to make it work without a realated file with a getfield("") but I have

encountered two obvious problems

1. Getfield is not updating instantly

2. Got me an infinite L blush.gifblush.gif P .....

 

Thank you all for helping out...

 

Gonna vote 4 u all laugh.gif

Link to comment
Share on other sites

Hi XtriM,

Glad to hear you found a solution of sorts for your requirements.

 

Interestingly, with a couple of minor changes, the file I referred to in my earlier post could be made to work directly off the status field, calculating dynamically without relying on scripts or on the Today function.

 

To do this is simply needs some extra code to impute the status values since the last change to the status field, plus the usage calc must be made unstored so that it will update over time.

 

Thus if you wished, you could still implement a single file solution which will be suitable to run on your server, and without recourse to plug-ins.

 

An updated copy of the example file which shows the changes necessary to achieve this is online at:

 

http://www.nightwing.com.au/FileMaker/demos/Usage2.zip

 

Not a problem if you don't want to use it, but I thought you might like to have the option. wink.gif

Link to comment
Share on other sites

  • 2 months later...

Hmmn... long time 'between drinks' on this one!

 

If your status field cannot be indexed, that would seem to indicate that it is an unstored calculation. That being the case, any calculations which reference it be unstored as well - and since the solution I suggested back in March utilises calculations to accumulate a history, it requires that the status field be stored.

 

There are perhaps a few ways around that, but the simplest might be to have a script which transfers the Status to a stored field from which the history can be compiled.

Link to comment
Share on other sites

BTW

 

It has some problems with calulation that I'm unable to solve like : negative values and unexplainable usage results....

 

Maybe you can help me here one more time?

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use