Edit the value in another table

demo_demo · 08-22-2020 08:38 AM

I am having table department
Department table holds the column Dep Code and Dep NO ID as number data type
I am having another table has Members
I am having the department column as text and i am using select expression in suggested value
When i create a row in the member table with select one of the department code
I need to increment the value of Dep No ID in the Department table with +1 only if the Member Department = Department code
Suggest any expression??

Marc_Dillon

Are you wanting to show a count of the number of members per department?

demo_demo

Yes ,I need to count.

Marc_Dillon

If the [Department] column in members table is a Ref-type, then you’ll have an auto-generated column in Departments table, called [Related Members]. Create a new virtual column in Departments table with the expression:

COUNT( [Related Members] )

If [Department] is NOT a Ref-type column, you can still accomplish the same count in the new virtual column with this expression:

COUNT( FILTER( Members , [Department] = [_THISROW].[Dep Code] ) )

demo_demo

Actually,I have a department code 001 and Dep Code Id as 1

If I save a row in member table with this department 001 ,then the dep code Id should change to 2

Marc_Dillon

Sure, I just guessed which column in Departments table was holding the key value, feel free the change [Dep Code] in the second expression to whichever column it should be.

You said in your first post that [Dep NO ID] column is the one that needs incremented (or a count of members was my suggestion instead of incrementing). Has this changed or did you just mis-type?

Sounds like you can just add 1 to the count expressions then if the count should start at 1?

COUNT(…) + 1

demo_demo

Yes ,I just mis type, actually, department table is not ref to member table just I am get the value by suggest values.
I need the dep code id should be increment by 1
When I select the department code in the member table.