Jump to content
  • 0
Sign in to follow this  
Batchini

Get Minimum Value on Insert and Update

Question

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. 

Share this post


Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0

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();
 

Share this post


Link to post
Share on other sites
  • 0

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? 

Share this post


Link to post
Share on other sites
  • 0

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. :rolleyes:

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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

 

--

 

Share this post


Link to post
Share on other sites
  • 0

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

 

image.thumb.png.cd7380ee8c0c6126a1913a2a5aa82f3f.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...