Help with Key Value and Template

[Martina_Hawkins]

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.

[Aleksi]

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

[Martina_Hawkins]

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

[Aleksi]

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

[Martina_Hawkins]

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

[Martina_Hawkins]

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?

[Martina_Hawkins]

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

[Aleksi]

Excellent!

[Martina_Hawkins]

@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.

[Martina_Hawkins]

I got the Filter working

Still can’t figure out the missing Item Id though

[Martina_Hawkins]

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

[Aleksi]

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

[Martina_Hawkins]

did that and got same results

[Martina_Hawkins]

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]>>

[Aleksi]

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

[Martina_Hawkins]

ok thanks