Help with Key Value and Template

I have a table Equipment
Item Id - Key
Item Category
Item Description
Location

I have a table called Costs
Cost Ref No - Key
Item Id
Cost Type
Cost

I have a table called Total Costs
Summary Id - Key
Total Costs

Total Costs is calculated in Excel - Sum of column Cost in Costs Table

I have a workflow to create a Costs Report
Workflow is based on Total Costs so Total Costs will show at top and then details below.
Item Id Category, Description, Cost Type, Cost

I recently changed the structure of the Costs table. Previously the key was Item Id. I had to change this to add a Costs Ref No as an Item may have multiple costs - Purchase, repair, service costs

The workflow worked before but not know with the new key

I have a VC in Costs called Category to pull the Category in from the Equipment table

This no longer works with the Key change and therefore the report cannot find Category

Any help appreciated.

If the “Item ID” is a ref field, you can read the category with a deref… [Item ID].[Item Category]

1 Like

@Aleksi, could you explain this a wee bit more please

Is the “Item ID” a Ref field in your Costs table? If yes, is the source an Equipment table?

No it is not but I will try to make it a Ref to Equipment table

I have now made Item ID a Ref to Equipment table.

I have an inline view of the Costs in the Equipment View
When I select Add, It creates a record with Cost Ref No ( Unique id) and the Item Id filled in.

As the Item Id is now a Ref Value the users have a drop down list and can change the Item ID which I don’t want them to do but I think I can make it not edible if View is form. Is that correct?

Thank you for your help yet again @Aleksi. Report is now working :grinning:

Excellent!

@Aleksi, I was wondering if you could help me again with similar issue to above.

Trying to produce an Excel File showing List of Equipment at a particular location and include latest PAT Testing info.

Equipment Table has a [Current Location] field.

I have a Usersetting for user to select their Location.

I have a PAT Check table similar to Cost Table
PAT Check Ref No - Key
Item Id - Ref to Equipment - Item Id
PAT Check Date
PAT Check Due Date

I have a Slice PAT Check Due with filter
(
[_THISROW]
= MAXROW(
“PAT Check”,
“PAT Test Due Date”,
([_THISROW].[Item Id] = [Item Id])
)
)

I have a Table Location
Location
Inspection Date

Equipment [Current Location] is a ref to Location.
I also have a slice on Table Location.
This slice has a filter using Usersettings

Workflow template is based on filtered Location table.

Output is as below with usersettings entered as Millbrook Park

I don’t know how to filter the table by UserSettings and the Item ID is missing when there is more than 1 record in the PAT Check table. It does pick up the latest PAT Test date info but I don’t know why the Item Id is missing.

I got the Filter working :grinning:

image

Still can’t figure out the missing Item Id though

Item Id seems to be missing for 1st record in table
Above is when ACC001 and ACC002 have Location = Millbrook Park

Below is when only AC0002 has Location - Millbrook Park

Please test what happens if you remove everything between Row#1…Row#6

did that and got same results

I had a missing >> but made no difference.
Removed TRUE, no difference.
Tried [Item ID].[Item ID], no difference

<<Start: SELECT(PAT Test Due[PAT Check Ref No],([Item Id].[Current Location]=Usersettings(“Job Name”)),TRUE)>> <<[Item ID]>>

@Martina Would you please send a support request to support@appsheet.com and we will check the reason for this behavior, thanks.

ok thanks