I would like to generate list of values from related field but with empty and non-empty values. Standard List() function removes empty values. I was looking for custom function but didn't find any good one.
For now I just edited awsome CustomList custom function so it doesn't remove empty values. It works but I feel that is a to much code for such a simple problem.
Can you recommend me any solution for that problem?
Are you trying to create a value list, or use the list to report other related values where a blank value would be important? If you're looking for a unique value list of the related records, a value list pointed at that table would work, alternatively, you could use SQL to report unique values.
I have related values like that
Table
| ID | Value |
|---|---|
| 1 | Apple |
| 2 | Orange |
| 3 | |
| 4 | Banana |
| 5 | Apple |
| 6 | Nuts |
I would like to get string like that "Apple¶Orange¶¶Banana¶Apple¶Nuts" - just list of every related value (just related to current record, not all from related table).
I tried to use ExecuteSQL but it worked without context therefore it listed all values from the whole related table. Probably I can make SQL query more specific but I'm not that good at it.
What is the value/field that is related?
Sent from miPhone
Beverly is right, what is the related value? You'll need that value to limit the list of an item.
My mistake I didn't provide sample file on the beginning. Please open attached file.
ExecuteSQL ( "
SELECT \"name\"
FROM RelatedValues
WHERE \"_mainTableID\" = ?
" ; "" ; "" ; __IDpk )
This calculation can be stored but if so value will change only when __IDpk is changed.
I tested the SQL on your sample file then got same order of portal, but generary SQL without ORDER BY return result in random order, so you'd need it with relation (or portal) sort field, or creation order if they are not sorted.
If "any" solution includes copy/paste, then here it is...
Hi,
thank you all for your help. With all 3 methods it is possible to choose the most convenient for any situation. To summarise:
1. When relation conditions are complex and they are already on graph then I would choose modified CustomList ().
2. When there is no relation or its simple to recreate with ExecuteSQL, then it's way to go.
3. When it's just matter of copy paste to some filed the solution showed by siplus it's great.
ExecuteSQL ( "
SELECT \"name\"
FROM RelatedValues
WHERE \"_mainTableID\" = ?
" ; "" ; "" ; __IDpk )
This calculation can be stored but if so value will change only when __IDpk is changed.