pull information from a field in another table from the respective field based on the month of the year

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
I have two tables. "Processing" and "Monthly Interest Rate".

I have a field in the processing table that is called [interest rate]
I have fields in the "interest rate table" called [JAN 2018], [FEB 2018], [MAR 2018] and so on.

I want to pull the value of the corespondent field from table "Monthly Interest Rate"  
If we are in the month of February of 2018, than the rate that would apply is the value on the field [FEB 2018] and it would fill out the field [interest rate] that is in the table "processing".

I would appreciate if someone could help me make that formula?
Thank you!
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
You can set the Key field of your interest rate table to be a date field of the first of the month. Then in your detail table make a formula field of

FirstDayOfMonth ([My date field])

Them use that to make a relationship to the interest rate table and the lookup the interest rate.
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Hey Mark,

Thank you so much for your help. I dont think i was able to accomplish what you said.
I made a quick 1 minuto video to explain better what I am trying to do here. If you or anyone could help me figure the best way to do this, It would be very much appreciated.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 48,586 Points 20k badge 2x thumb
The video would not play for me. I’m outside the US. Would that be why?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Sergio

I read your question again.

The best way to do the tables is to have a separate record for each month in the interest rates table. Set the key field to be a date field type and enter the date for the first of the month in each record along with the interest rate.
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Thank you! 
I will try again. Really Appreciated! 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Sergio,
Do you have it working now?
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Mark, not yet.
I think there is something missing.
Its probably easier for you to understand if you were able to see the video. I dont understand why you cant if I didnt restrict the region.

Anyways, I created the records, one for each month with the interest rate for each month.

In the other table where I have the transactions, each transactions has its value, and its interest rate according which month it is. 
What I needed is if I have 100 transactions on Jan, the interest rate would be whatever the interest rate it is on the interest rate table. 

Right now the way I do is, change the default value of that field in the begining of the month, so every record I import after that date, will have that default value on the rate.

Even the guys on the quickbase support team were not able to get that working for me.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Sergio
I did watch the video via the link you posted.

What you want to do is very simple, so it will work.

Please answer these questions.

1.  Do you have a table of Interest rates, with 1 record per month.

2. Is the key field a date field?

3. Is that Key field loaded with the 1st of each month.

4. Do you have a field on the detailed transaction records with a formula of FirstDayOfMonth([Transaction date])

5. Is there a relationship based on that FirstDayOfMonth([Transaction date]) field?

6. Are you puling down the interest rate in that relationship?
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Hi Mark,

Yes i did. 
I does work for records that have the data of transaction on the first day of the month, but its does not work for the other days.
Dont I need to add something to the formula to pick up the other days?
FirstDayOfMonth([DATE OF TRANSACTION])
It only works for transactions done on the first day, the other days it doesnt work,
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
No, that is correct.  What field did you use the build the relationship?  Maybe you used the transaction date to build the relationship and not the formula field for the first day of the month.  You need to use that formula field.
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Got it!!! 
Works!
Thank you so much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 48,586 Points 20k badge 2x thumb
So in the end, did you simply do what I originally posted?

You can set the Key field of your interest rate table to be a date field of the first of the month. Then in your detail table make a formula field of 

FirstDayOfMonth ([My date field]) 

Them use that to make a relationship to the interest rate table and the lookup the interest rate.
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
I got one other formula that support was not able to do it. If you have a chance to take a look it would be a blessing!

https://community.quickbase.com/quickbase/topics/how-to-create-a-formula-field-that-will-add-all-tra...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 49,512 Points 20k badge 2x thumb
Sergio,
I think I would have to look at your actual app on a screen sharing call, but i would need to be on the clock, for that time.  Contact me via the email  address on my website f you would like to email directly with me.  QuickBaseCoach.com
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Hey Mark.
Thank you so much for all the help! 
I figure what the problem was.
It works perfectly!
Really appreciate it.