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

• 0
• Question
• Updated 1 year ago
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!
• 492 Points Posted 1 year ago

• 0
• 72,448 Points 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.
• 492 Points 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.
• 71,088 Points The video would not play for me. I’m outside the US. Would that be why?
• 492 Points • 72,448 Points Sergio

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.
• 492 Points  • 492 Points Thank you!
I will try again. Really Appreciated!
• 72,448 Points Sergio,
Do you have it working now?
• 492 Points 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.
• 72,448 Points Sergio
I did watch the video via the link you posted.

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

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?
• 492 Points 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,
• 72,448 Points 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.
• 492 Points Got it!!!
Works!
Thank you so much!
• 72,066 Points 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.
• 492 Points 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!

• 71,138 Points • 492 Points 