Initialize Item List with an integer form field

0

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

Hi Daniel!

Thank you for your kind answer!

I've tried the first query and Webcon is complaining with the error I've attached.

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

You add this in the initialization?
My field is type integer (to substitute in the '3') so I'm not understanding what Webcon is actually complaining

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}'
)

Hi Andreia,

your union would do the following:
1
2
3
4
Date

But you want
1, 2022-02-02
2, 2022-02-02

So it should look similar to:

select MonthNumber as {DCNCOL:554},
'{ISO:3888}' as {DCNCOL:557}
from (
select TypeId as MonthNumber,
null as Dates
FROM [dbo].[DicFieldDetailTypes]
where TypeID between {3887} and 12
)
At least I think that ISO3888 refers to the ISO formatted Form field. Which should be repeated for every month and stored in item list column 557

Best regards,
Daniel
Best regards,
Daniel

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