Lookup value in report from a child table

Seth_Berman · 04-12-2021 09:55 AM

I have a report running on a table containing our list of clients that needs to pull a list of values from a child table which contains order details for the clients. The values themselves will just be barcodes and I would like to use the lookup command to pull the corresponding product names from the product table.

The report has a START loop using this filter which identifies the correct clients who have had transactions within 30 days and we are pulling other fields for the report properly…

<<Start: Filter(Client Profile, AND(ISBLANK([Date Offboarded]), OR(MAX([Related Sales Helpers][Date])>TODAY()-30,MAX([Related Specimen Counts][Date])>TODAY()-30)))>><<[Client]>>

[Related Sales Helpers] contains 1 or more transaction rows from the child table for each client.
I tried the below expression without the TEXT at first but I was getting a couldn’t match list to text error. Adding the TEXT seemed to work but only returned the first value. Putting the Lookup expression within its own nested START: loop also returned an error.

<<LOOKUP(TEXT([Related Sales Helpers][Product Barcode]),“Product”,“Product Barcode”,“Product Name”)>>

Marc_Dillon

This is probably what you need to do. What was your full START expression here, and what was the error?

Seth_Berman

As soon as I warp it in a Start loop I get this in the log.

Attachment template. Start expression ‘LOOKUP(TEXT([Related Sales Helpers][Product Barcode]),“Product”,“Product Barcode”,“Product Name”)’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values.

Marc_Dillon

A LOOKUP() expression will NEVER generate a List of Refs. (edit: actually I’m doubting this absolute statement, but what you’re doing is still definitely far from correct)

Maybe:
<<START: [Related Sales Helpers][Product Barcode]>>
is what you’re wanting.

https://community.appsheet.com/search?q=start%20expressions

Seth_Berman

Thanks for the clarification on using LOOKUP(). We have been successful producing the list of related product barcodes from the child transaction table Sales Helpers, the issue is that within the app the Product table uses the Product Name column as the label. Product Barcode is the key column of the product table. When a transaction row is recorded it stores the Product Barcode and any views in the app will return the Product Name. In the report system however it seems to ignore the labels, hence why we were trying to use the Lookup() function.

These tables are used in a number of other places and app modules, I suppose i can look into how much of a pain it would be to simply add the Product Name column to the Sales Helpers table and have it populated using LOOKUP() when the transaction row is recorded to the table.

Marc_Dillon

I am indeed fully versed on how keys vs labels work.

One of the main points here is this:

Emphasis on the “or more”. You cannot simply pull a single value, from a single child record, into your parent record.

I think you’re thinking this is far more complicated than it really is. Your template should just have this general structure:

<<START: parent-records>>

<<START: [related childs][child-key]>>

<<[Product Name]>>

<<END>>

<<END>>

Alternatively, if there really is only 1 child, you can use:
ANY( [related childs][Product Name] )
to pull the single value.

Seth_Berman

Thanks again. Lol, glad you are well versed, I seem to find some new rule every time I try to do something!

The goal in this specific case is to grab all the products from the transaction table that had transactions during the 30 day time period of the report. The issue as I mentioned was that with the current data structure the Product table is not directly related to the Client table and the table in the middle, the Sales table currently only has the Product Barcodes recorded. To get all this on a single report, I think this will be easier if I add the Product Name to the Sales table in the middle so the column can be pulled into the report the way we are doing the rest of them. Thanks again for your insight.

Marc_Dillon

Ohhh, I see.

I think you can then simply replace the
<<[Product Name]>>
portion of the above example template with:
<<[Product Barcode].[Product Name]>>

Seth_Berman

thanks!