Home > Forum > Actions > Initialize Item List with an integer form field

Initialize Item List with an integer form field
0

Hello all,

I'm trying to set up a payment process in webcon for Rents. The idea is:
- Open request and save the date of request (as the current date)
- Calculate how many months until end of year (civil year)
- Initialize the item list with the number of rows as the number of months
- Each row will create independently the payment for that month

Although I have all the idea structured, I'm facing some questions in regards to:
- How can I initialize dynamically the item list with an information from an integer form field? Didn't find anything in the options using SQL query
- Is it possible to translate the number of months as text field, example, from today until end of year, we have September/October/November/December, or at least, have in each row the month number

I'm feeling that this is will be impossible to achieve!

Hope anyone has a brilliant idea!

Thank you!

MVP

Hi Andreia,

you can use this query to initialize the number of rows, where you can replace 3 with your field.

SELEct TypeId as MonthNumber
FROM [dbo].[DicFieldDetailTypes]
where TypeID between '3' and 12

I would choose a multilingual picker field for the Months with the following sample query:
select '1' as Id, 'January$$DE$$Januar' as Label union
select '2' as Id, 'February$$DE$$Februar' as Label union
select '3' as Id, 'March$$DE$$März' as Label union
select '4' as Id, 'April$$DE$$April' as Label union
select '5' as Id, '' as Label

If you save the MonthNumber from the query in the picker field, it should display the corresponding month in the defined language.


Best regards,
Daniel

In reply to: Daniel Krüger (Cosmo Consult)

Hi Andreia,

this time you need to pick the value from values tab since you want to use the current value from the form and not some value saved in the database.
At least is my assumption, since the database column is displayed.

Best regards,
Daniel

Hi Daniel,

Thank you for your message! I was able to make it work! thank you so much!

I have just one more question, is it possible to combine the initialization of the item list with the Month Number using your query and add in another column a copy of a date form field?
I've tried to combine both queries with a UNION ALL but it does not work and doing an Update Item List breaks the initialization...
I'm probably setting this in the wrong way!

select MonthNumber as {DCNCOL:554},
Dates as {DCNCOL:557}
from (
select TypeId as MonthNumber,
null as Dates
FROM [dbo].[DicFieldDetailTypes]
where TypeID between {3887} and 12
union all
select
null as MonthNumber,
'{ISO:3888}' as Dates
from WFElements
where WFD_ID='{WFD_ID}'
)

MVP
In reply to: Andreia Correia

Hi Daniel,

Thank you so much for your kind answer.
I've tried but Webcon is complaining of the last ')' .
I can't check this in SQL syntax because of the fields that I'm getting from the form.

If you haven't got the same issue, I will possibly check out this with the support!

Thank you!

Hi Andreia,

I should have checked my reply on the PC. There's no need for the brackets and outer select anyway.

select TypeId as {DCNCOL:554},
'{ISO:3888}' as {DCNCOL:557}
FROM [dbo].[DicFieldDetailTypes]
where TypeID between {3887} and 12

This should bring you the same result.

Best regards,
Daniel