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
------------------------------
- MikeTamoush7 months agoQrew Commander
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
------------------------------- DeepaPrashant17 months agoQrew Cadet
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
------------------------------- MikeTamoush7 months agoQrew Commander
Ahhh, I see what you are saying. Let me think on that.
------------------------------
Mike Tamoush
------------------------------