Variable drop down field
Hello,
I have an interesting request. My QB app is pulling in entries and I am capturing the posting date of the entry / record. I can convert the posting date to a fiscal period such as Oct'23 usi...
Forum Discussion
Correcting and clarifying my question above.
Using API's to create records in QB.
I have an interesting request. My QB app is pulling in entries and I am capturing the posting date of the entry / record. I can convert the posting date to a fiscal period such as Oct'23 using a formula field. I need the user to now select the fiscal period in which the entry will be reversed. I initially just created a static drop down field but received a request to make it variable so it only shows (in the drop down list) future fiscal periods.
So if posting date is 10-01-2023...Fiscal period (Formula)= "Oct'23"
Drop down list should show:
Oct'23
Nov'23
Dec'23
Jan'24 etc
If posting date is 05-29-2023 and Fiscal period (Formula) is "Jun'23" as May 29th is fiscal June in our company.
Drop down list should show:
Jun'23
Jul'23
Aug'23
Sep'23
Oct'23
Nov'23
Dec'23
Jan'24 etc
What's the best way to achieve this?
Thanks.
Deepa
------------------------------
Deepa Prashant
------------------------------
I would First Create a table called Months and Fiscal Years. There would be 4 fields: Month, Year, Fiscal Year, Last Day of Month (date field)
Preload this using an Excel Sheet import, for the next 10 or 20 years.
Use a relationship where One Month and Fiscal Year has Many of your Current table.
On the current table, Make a formula that simply extracts the fiscal year from your Posting Date=Fiscal Year in New Table. Then use a conditional dropdown to only show the pre-loaded entries where Fiscal Year from Posting Date. The report you would use would have a filter Last Day of month>=Today(). This would eliminate all the past months.
------------------------------
Mike Tamoush
------------------------------Thank you Mike.
The trouble is in getting the prior periods to not show. The Posting date need not be today. The analysts could be editing the record a week from the Posting date and I need the drop down to only show the current period onwards.
So based on the Posting Date in the Child table is there a way to create a conditional drop down and pull all future periods as a drop down from the parent table?
Thanks
------------------------------
Deepa Prashant
------------------------------Ahhh, I see what you are saying. Let me think on that.
------------------------------
Mike Tamoush
------------------------------
