StephenWonfor

Variables in List()?

Discussion created by StephenWonfor on May 3, 2013
Latest reply on May 7, 2013 by StephenWonfor

HI

 

I have a homemade client database that has a table with 20,000 or so records and 200+ fields. I am trying to find out how many of the fields do not contain data.

 

1. create a layout with all non-global and calculated fields on it.

2. go from field to field and do an "InsertFromIndex" on each.

3. Note fields where no data is present.

4. Remove same.

 

This would, I expect to take "x" hours.

 

But, as a developer, I concluded there must be an more elegant and easier way. I thought I could use the FieldNames design function to create an abstracted bit of code that would loop through all the fields and use List() (via a cartesian join) to get the field contents, noting those where contents were empty.

 

Needless to say I am at "3x" hours to date...

 

Go to Layout [ “ComponetsViewer” (ComponentsFocal) ]

Show All Records
Set Variable [ $AllFields; Value:FieldNames ( Get ( FileName ); Get ( LayoutName )) ]

Set Variable [ $AllFieldsCount; Value:ValueCount($Allfields) ]
Set Variable [ $$Results; Value:"" ]
Set Variable [ $Count; Value:$Count + 1 ]
Loop

Set Variable [ $Field; Value:"componentsfocal|x|" & "::" & GetValue($AllFields; $Count) ] //error lies here???

Set Variable [ $Contents; Value:List ( $Field) ] // the issue is here. hardcode works, variable fails
Set Variable [ $ContentsCount; Value:ValueCount($Contents) ]

Set Variable [ $$Results; Value;

Let( [

x = $ContentsCount;
y = Case ( IsEmpty(x) or x = 0;0;x) ];

Case( not IsEmpty($$Results); $$Results & "¶") & $Field & " - " & y

)]
Set Variable [ $Count; Value:$Count + 1 ]

Exit Loop If [ $Count > $AllFieldsCount ]

End Loop

 

Does List() not like variables or is this a flaw ( #2,245 in a series) in my code. I find I get the same issue using CF's like FoundList(...Eilert Sundt) and UniqueValues(Jeroen Aarts then tweaked by Erik Wegweiser)

 

 

Stephen

 

 

"When you are courting a nice girl an hour seems like a second. When you sit on a red-hot cinder a second seems like an hour. That's relativity." ---- Albert Einstein

Outcomes