Field populating determined by current month field value. How to?

[AbsoluteVoice4u]

Field populating determined by the current month FIELD value. How to?

 

Hello all. I'm looking to populate a "Budget months" field automatically for all records and have it update automatically, month to month, relational to the current month. I have a "current month field" in place that always reflects the current month. The "current month field" is a text field". For all twelve months of the year, I would like to have the "Budget months" field auto populate similar to the following:

 

Current Month / Budget months

March / 8

April / 1

May / 4

As you see, each month would have it's own corresponding number permanently assigned, that would not change. I need to have the "current month" field automatically populate a "Budget months" field. What's the best way to accomplish this, please?

[Miss A!]

 

Try this calculation for the budget field to set value when a particular moth name is set;

 

Case(
lxd_month = "March"; lxd_month & "/" & 8;
lxd_month = "April"; lxd_month & "/" &1;
lxd_month = "May"; lxd_month & "/" &4;
"Current month not set"
)

 

*Complete syntax for all months*

 

 

[AbsoluteVoice4u]

I totally appreciate you helping me and your suggestion is working but it does not automatically update all existing records. It only enters the number when I create a new record. How can I have the field populate correctly, depending on the current month, as I view existing records, please?

[LaRetta]

Hello there stranger!

 

You do not need to store the 'current month' in a field since it can be known at any time with:

Month ( Get ( CurrentDate ) 
or
MonthName ( Get ( CurrentDate )

To have information update constantly according to the current date or time, you need to use a calculation and check 'do not store calculation results' in its storage options.

 

I do not see the correlation between the month listed and the number - can you explain it for us?  Knowing the purpose of this concatenated field 'month / number' will help us come up with the best solution for you.  :-)

[AbsoluteVoice4u]

LaRetta! Hello there, you dear woman.....TOO long no talk.....

 

When someone signs up for a propane account, the particular month they sign up in determines the number of months they are offered budget billing. So, I'm looking to have the budget_months_avail auto populate.....and even for existing records, not just ones newly created.

It goes something like this:

July = 12

August = 12

September = 12

October = 8

November = 7

 

The number of budget months available is predetermined, so I could use a value list. My thought is to have the budget_months_avail field automatically trigger of the records current_month field. The trick is to have it work and autopopulate for all records, not just newly created ones. It does not have to maintain the budget_months_avail when the person signed up. There's the long and short of it. .....Thank you in advance....yer a dear...yes ya are.

[comment]

When someone signs up for a propane account, the particular month they sign up in determines the number of months they are offered budget billing.

 

Do you have a field for the particular date on which they signed up? If yes, you should use its month to determine the number of discount months, rather than the current month - which for existing records may no longer be current. And the preferable method, IMHO, to determine this number would be by using a lookup, not a calculation. Because IIUC this number is just another business rule (same as price) and could be changed in the future. To populate past records that did not perform a lookup at the time they were created, use a relookup.

 

OTOH, I don't understand what you mean by:

 

It does not have to maintain the budget_months_avail when the person signed up.

 

This seems to suggest something else altogether?

[AbsoluteVoice4u]

In a nutshell, I'm looking for a field to be populated in every record, at all times, that reflects the current number of budget months available and the budget months available is derived from the current month. If someone signs up in July and I look at the record in October, the number of budget months will be different and that's perfectly ok.

 

I'm thinking that having the current month reference a value list would do the trick?

 

I'm having difficulty structuring the field to auto populate each record.

[comment]

I am having difficulty understanding what you're saying.

 

Perhaps we could use a worked-out example: suppose I signed up in October 2014. If understand correctly, anyone who joins in October receives 8 months of budget billing. Now, assuming that the first one of those 8 months was October 2014 (is that correct?), then at the beginning of the current month (January 2015), 3 of those 8 months were already used up, with 5 remaining. Tomorrow, when February 2015 begins, 4 months will have elapsed, and 4 will still be remaining. Is that the information you are trying to display?

[AbsoluteVoice4u]

Let's say I want a field named BB to auto populate, relational to the month in which I viewed the record.

 

July, would have BB show a 12

August, would have BB show a 12

September, would have BB show a 12

October, would have BB show a  8

November, would have BB show a 7

December, would have BB show a 6

January, would have BB show a 5

February, would have BB show a 4

March, would have BB show a 12

March, would have BB show a 12

April thru June would show BB a 12

 

What's the way to accomplish this, perhaps using a value list? Or, would you suggest another method? 

[LaRetta]

... relational to the month in which I viewed the record.

 

Relational to when you are NOW viewing the record?  Or relational to when you viewed the record at some other time, such as when the entry was created?  It is still not making sense the purpose of your request.  If all you want is to, for February, have a 4 display in a calculation then you can create a CALCULATION, result is number with:

Let ( 
m = Month ( Get ( CurrentDate ) )
;
Case (
m = 2 ; 4 ;
m = 1 ; 5 ;
m = 12 ; 6 ;
m = 11 ; 7 ;
m = 10 ; 8 ;
12
) // end case
) // end let

This should NOT be data because it cannot update according to the current date ... only unstored calculations can do that.  Then, while in your calculation dialog, you must also go to Storage Options and check 'do not store calculation results' so that number changes when the current month changes.  This then will display the number based upon the current month so Feb would show 4.  Is this what you want?

 

It is the same as having a list laying by your computer where you can see WHAT the number would be for the current month and has nothing to do with the record you are on.  If we are still missing the mark, you need to tell us the purpose. :-)

 

edited ... repaired code box

Edited February 1, 2015 by LaRetta

[comment]

I would not suggest a method before understanding the purpose that the method is supposed to accomplish. This purpose, I am sorry to say, is getting less and less clear with each post of yours.

 

I will say this: a field does not "auto populate, relational to the month in which I viewed the record". A calculation field can be either stored or unstored. If it's stored, it will be re-evaluated if - and only if - one of the referenced fields is modified. If you want a field to display a result that depends on the current date (i.e. the date on which it's viewed by you - or, more precisely - the date on which the layout was refreshed), you must make it an unstored calculation field.

[LaRetta]

I should add that business data should not be inside calculations because, if you change one of those numbers, you will need to have a developer open that calculation and change it.  Ideally, this should be a record or a table holding this information.  Without knowing the purpose of this exercise, I agree with Comment completely.  It would help if you forget what you think you need and just tell us how you will be using this information.  Pretend you are explaining this part of your business to someone and they need to know ... what?  Is it so that they can tell a person, "Hey, if you sign up this month, you will get 4 budget months!!"

 

So again, the purpose please. :-)

[AbsoluteVoice4u]

Thank you. The purpose is for the person keying in a new account to be able to see a field that tells them the number of budget months available to offer the prospect.

[comment]

The purpose is for the person keying in a new account to be able to see a field that tells them the number of budget months available to offer the prospect.

 

Well, do they need to see this number before they create the actual account? Or would it be sufficient for them to create the account first, which would then lookup the number of budget months into a field in the Accounts table (and which, if so desired, the person creating the account could modify per customer)?

[AbsoluteVoice4u]

Great question.............the record typically exists and would not be created on the spot. So, yes the info is required before they would create an account. It seems simple but it also seems complex to accomplish. To me, it's akin to having todays day and date show up automatically on each record, automatically. No?

[comment]

I believe you should start by building a BudgetMonths table with (at least) two fields and 12 records, looking something like this:

---------   ------------
MonthName   BudgetMonths
---------   ------------
January     5
February    4
March       12
April       12
May         12
June        12
July        12
August      12
September   12
October     8
November    7
December    6

Now, if they really need to see the number relevant to the current month before they create an actual account with an actual date, then you could show this in a one-row portal to the BudgetMonths table, filtered to show only records where =

BudgetMonths::MonthName = MonthName ( Get (CurrentDate) )

--

 

To me, it's akin to having todays day and date show up automatically on each record, automatically. No?

 

No, because showing today's date is dead simple using (for example) an unstored calculation field = Get (CurrentDate). Here you want to use the current date in order to pick an item from an array of data. Not the same thing at all.

[Lee Smith]

Please play nice. I have removed the posts that were of a personal attack.

[AbsoluteVoice4u]

I believe you should start by building a BudgetMonths table with (at least) two fields and 12 records, looking something like this:

---------   ------------
MonthName   BudgetMonths
---------   ------------
January     5
February    4
March       12
April       12
May         12
June        12
July        12
August      12
September   12
October     8
November    7
December    6

Now, if they really need to see the number relevant to the current month before they create an actual account with an actual date, then you could show this in a one-row portal to the BudgetMonths table, filtered to show only records where =

BudgetMonths::MonthName = MonthName ( Get (CurrentDate) )

--

 

 

No, because showing today's date is dead simple using (for example) an unstored calculation field = Get (CurrentDate). Here you want to use the current date in order to pick an item from an array of data. Not the same thing at all.

 

Thank you, Thank you, Thank you.....that's the ticket!

  ...and thank you also LaRetta!

Great people here....simply great!