Huge array, text funnel

I’m looking for some creative problem-solving help. Some time ago, Dave came up with this crazy FormulaFill that has been working great for filling a field in a summary record. That thread is here: Formula fill summary records? for context. It explains the bigger picture of what I’m trying to do.

This is the code:

Field Order
FormulaFill ?(info("summary") =0,«»,collector+assign((sandwich("",collector,¶)+arrayfilter(«»,¶,{import()+¬+str(OrderNo)}))[1,info("summary") =0],"collector"))

Now, I’m trying to do the same thing, only summarizing ~10 orders, I’m trying to summarize 90+ orders. This ends up being too much for the Order field to hold. So I thought if I could put the array in a variable, I could print the report that I need and then the array can go away. I don’t need to store it in the database. But huge_order only ends up containing a single order, not all 92. Text funnels are still really unfamiliar to me and I don’t know how to edit this code to do what I want. Is it possible?

global huge_order
Field Order
huge_order = ?(info("summary") =0,«»,collector+assign((sandwich("",collector,¶)+arrayfilter(«»,¶,{import()+¬+str(OrderNo)}))[1,info("summary") =0],"collector"))

Could you show us an example of what the desired result should look like for say 3 or 4 orders?

Sure, I’ll try. Each record in the database represents an order, so the Order field for each record contains an array with multiple items. Here’s what the Order field would look like for three sample orders:

Order 30056:

  1)	6315-B	  3	   3	               	 16.00	  48.00	'Auten's Pride' Peony  OG
  2)	6316-D	  2	   2	               	 40.00	  80.00	'Benjamin Franklin' Peony  
  3)	6317-D	  1	   1	               	 43.00	  43.00	'Bowl of Beauty' Peony  
  4)	6318-D	  2	   2	               	 43.00	  86.00	'Duchesse de Nemours' Peony  
  5)	6319-C	  1	   1	               	 22.00	  22.00	'Felix Supreme' Peony  OG
  6)	6321-D	  4	   4	               	 43.00	 172.00	'Koppius' Peony  
  7)	6322-D	  1	   1	               	 33.00	  33.00	'Sarah Bernhardt' Peony  

Order 30062:

  1)	6232-A	  1	   1	               	 14.00	  14.00	Music Garlic ECO
  2)	6240-A	  1	   1	               	 14.00	  14.00	Russian Red Garlic ECO
  3)	6261-A	  1	   1	               	  6.40	   6.40	'Chinese Sacred Lily' Paperwhite 
  4)	6265-A	  1	   1	               	  7.80	   7.80	'Ziva' Jumbo Paperwhite 

Order 30070:

  1)	6224-B	  1	   1	               	 48.00	  48.00	Inchelium Red Garlic OG
  2)	6229-C	  1	   1	               	 95.00	  95.00	German Extra-Hardy Garlic ECO
  3)	6235-C	  1	   1	               	 95.00	  95.00	Giant Turkish Red Garlic ECO
  4)	6553-A	  2	   2	               	  3.90	   7.80	'Pieton' Asiatic Lily 
  5)	6613-A	  1	   1	               	  3.40	   3.40	Guinea-Hen Flower Fritillaria 

In the original approach, I had a summary record where the Order field was filled with all of these orders combined, plus the order number added to each row, using the text funnel that you suggested:

  1)	6315-B	  3	   3	               	 16.00	  48.00	'Auten's Pride' Peony  OG 30056
  2)	6316-D	  2	   2	               	 40.00	  80.00	'Benjamin Franklin' Peony  30056
  3)	6317-D	  1	   1	               	 43.00	  43.00	'Bowl of Beauty' Peony  30056
  4)	6318-D	  2	   2	               	 43.00	  86.00	'Duchesse de Nemours' Peony  30056
  5)	6319-C	  1	   1	               	 22.00	  22.00	'Felix Supreme' Peony  OG 30056
  6)	6321-D	  4	   4	               	 43.00	 172.00	'Koppius' Peony  30056
  7)	6322-D	  1	   1	               	 33.00	  33.00	'Sarah Bernhardt' Peony  30056
  1)	6232-A	  1	   1	               	 14.00	  14.00	Music Garlic ECO 30062
  2)	6240-A	  1	   1	               	 14.00	  14.00	Russian Red Garlic ECO 30062
  3)	6261-A	  1	   1	               	  6.40	   6.40	'Chinese Sacred Lily' Paperwhite 30062
  4)	6265-A	  1	   1	               	  7.80	   7.80	'Ziva' Jumbo Paperwhite 30062
  1)	6224-B	  1	   1	               	 48.00	  48.00	Inchelium Red Garlic OG 30070
  2)	6229-C	  1	   1	               	 95.00	  95.00	German Extra-Hardy Garlic ECO 30070
  3)	6235-C	  1	   1	               	 95.00	  95.00	Giant Turkish Red Garlic ECO 30070
  4)	6553-A	  2	   2	               	  3.90	   7.80	'Pieton' Asiatic Lily  30070
  5)	6613-A	  1	   1	               	  3.40	   3.40	Guinea-Hen Flower Fritillaria 30070

If an ‘Order’ field actually contains a line number, a part number, quantities, prices, extended amounts, descriptions, etc, then it is time to tear this database apart and start over in its design. There is no reasonable reason to have a single field contain all of this information. This goes antithetical to any logical db design. This is an abuse of the capability of a db program. Just because you can, doesn’t mean you should. You are packing 10 lbs of stuff into 5 lb bags and wondering why the bags are breaking while the guys on the packing line are screaming for more bags, delivered quicker.

I agree. I would rather have each line of the order be its own record in another database. I inherited this database and associated files. But redesigning our databases right now is not possible, for a variety of reasons.

I’ve been known to be too blunt & honest at times when people don’t really want to hear the truth but as you’ve posted to the forum asking how to solve a problem, most often the first step in solving the problem of how to get out of a hole is to ‘Stop Digging’.

I think that it also needs to be asked for a bit more detail on, what is it you’re trying to accomplish with this? Maybe it doesn’t need to be packed into a singe record, but instead could be loaded into variables for the sake of a report or…? Maybe an array of summary records would work. Specifically - what is it you’re trying to accomplish with those 92 orders?

The goal is to create a sort of “batch cover sheet” for a collection of orders, so that we know the quantity of each item that we need to set aside in order to fill those orders. After creating the huge array, I load it into an unlinked database where each each line in the order gets its own record, and sort it by item number so that like items are together.

It doesn’t have to be packed into a single record, though that was the original approach. It would work fine to have the huge array stored in a variable–I just can’t figure out how to create the variable.

I guess I could loop through the selected orders and append the contents of the Order field to the unlinked database one at a time. I just try to avoid looping through records because it can be quite slow in linked databases.

No loops. It seems to be the perfect use for arrayselectedbuild.

Arghh… I was so focused on making slight modifications to the code that I had been using, I overlooked this option. I think it will work, though I’ll have to figure out how to get the order numbers into each row of the array. I won’t be able to work on this project again until sometime next week, but I think this points me in the right direction–thanks!

Field Order
arrayselectedbuild huge_order, ¶, "", replace(«»,¶,¬+str(OrderNo)+¶)+¬+str(OrderNo)

We simply use the replace( function to replace the carriage returns in each order with a tab, order number and carriage return. The last line in an order doesn’t end with a carriage return so we add a tab and an order number to the end.

Won’t that take the order number from the record I’m currently on, rather than getting all the different order numbers from the summarized order records?

arrayselectedbuild

Exactly

And - you can use variations on OpenFile to append an array to an existing database.

It will take the number from the record it is currently scanning. ArraySelectedBuild repeats its formula for each selected record. That formula can include as many of the database fields as you wish. Each succeeding repetition of the formula will take field values from succeeding records. We could have made OrderNo the active field, and then we could use «» to refer to it, and referred to the Order field by name. The result would have been the same. If all the fields are referred to by name, it doesn’t matter which of them is active.

Ah, great! Thank you.