Get Minimum Value on Insert and Update

[Kuroshi]

Hello Everyone!

So I have a parent table and a child table.

I would want my parent table to get the least amount of a certain field. 

I know this will be a trigger that will get the minimum value, but I can't get it to work.

Any thoughts?

 

Thanks. 

[ManokNaPula]

Hello Batchini!

Well, there would be two ways how to create this. If we're going to use trigger, this may be a complex one.

One easy way I thought of is with the use of Calculated Fields, but this would only be on a DataPage Level.

 

You can use this formula to get the Minimum Value. 

SELECT MIN(FieldName) FROM Child WHERE ID = target.[@field:FieldID]

Assuming if you have a foreign Key inside your child table. So we'll be using this formula to display the minimum value, Datasource would be your parent table. 

I hope this helps.

Scar();
 

[Kuroshi]

Thanks Scarlet, 

This would work but I would prefer that the values be saved in my Parent Table.

And yes, I forgot to give details.

This would be a very general description.

(Parent)Table 1 fields are: Project_ID, ProjectName, MinValue
(Child)Table 2 fields are: Work_ID, ProjectID, Status.

The status on the child table is represented by 0,1,2,3.

I would like to get the minimum status of the project ID stamped in the child table.

Does that make sense? 

[ManokNaPula]

Yes, that does make sense.

Here's a sample trigger that I created:

http://prntscr.com/p2uz6b

You need to create 2 triggered actions, one for Insert and another for Update. The reason is because, upon Insert, we only need to compare the inserted with the other values, if it is less than the minimum of the Min Value. 
But on Update, we need to compare the #inserted if it is less than the previous value and at the same time, compare if it is less than the Min Value inside the Table. 

Unless we create a separate trigger for the Update, the trigger will still recognize the previous value as an option to be the Min Value.

Here's the Update Trigger.

http://prntscr.com/p2v187

It's working on my end. 

[Kuroshi]

Seems very complex to me. hmmm. 

I hate to say this, but is there a way I can get a copy of the trigger that you have there? Maybe run some tests? I really owe you one. ❤

[ManokNaPula]

I can't upload it here, i dont know why I have this restriction. I tried, but i can only be allowed to attach a very small file. hmmmmm

Maybe I can send message it to you. 

[kpcollier]

Odd. My max total size is 3.13mb. Maybe it has to do with Rank (either the Advanced Member or Caspio Rockstar), but I remember having a problem about a week ago preventing me from uploading a file onto these forums.

Either way, @caspio should change the way it works.

Sorry I don't have any input into your problem. I can not think of another way to get the value from one table saved into another table, other than with Triggers.

[DefinitelyNot31337]

If you intend to filter your Parent records based on min(FieldName), Triggered Actions would be the way to go since Caspio does not have the capability to filter a report by a Calculated Field as of the moment.

 

Also, it might be worth mentioning that Triggered Actions are not available on Explore plans x).

 

--

 

[DefinitelyNot31337]

@Batchini, you may also consider this solution.

 

1.) Create a view. INNER JOIN on Table1.Project_ID = Table2.ProjectID.

2.) Create a Pivot Table using the View on item#1 as your DataSource

3.) For your rows, use Table1.ProjectName

3.) For your values, use Table2.Status; then set "Summarize values by:" to Min

 

Hope this helps

-DN31337!