topic Re: Determine status in App Development

Determine status

agbcn — Tue, 21 Dec 2021 20:02:52 GMT

Hi,

I've been struggling with a requirement and I can't find a proper solution.

Let's assume I have 2 tables Table A (work orders) and Table B (tasks related to work orders). Table A has a primary key (let's call it IdA) which is also a field in Table B. A work order in Table A will be closed once all its tasks are closed in Table B (status 0). So relationship between tables is 1..N (N could be 0) and status of a work order is given by the status of the tasks associated.

Table A

IdA Description New Calculated field

1 Order1 Open

2 Order 2 Closed

3 Order3 Open

Table B

IdB IdA Status

1 1 0

2 1 1

3 2 0

4 2 0

5 2 0

6 3 1

7 3 1

Thanks in advance

Regards

Àlex

Re: Determine status

RsQK — Mon, 21 Sep 2020 11:42:50 GMT

Hey, this should do it:

tempA: LOAD * INLINE [ IdA,Description 1,Order 1 2,Order 2 3,Order 3 ]; [Table B]: LOAD * INLINE [ IdB,IdA,Status 1,1,0 2,1,1 3,2,0 4,2,0 5,2,0 6,3,1 7,3,1 ]; [ml_open_closed]: MAPPING LOAD * INLINE [ From,To 1,Open 0,Closed ]; Tmp: LOAD IdA, Max(IdB) as IdB RESIDENT [Table B] GROUP BY IdA; LEFT JOIN (Tmp) LOAD IdA, IdB, APPLYMAP('ml_open_closed',Status) as NewField RESIDENT [Table B]; [ml_new_field]: MAPPING LOAD IdA, NewField RESIDENT Tmp; DROP TABLE Tmp; [Table A]: LOAD *, APPLYMAP('ml_new_field',IdA) as NewField RESIDENT tempA; DROP TABLE tempA;

Re: Determine status

agbcn — Tue, 22 Sep 2020 08:04:56 GMT

Thanks! It worked!

Regards

Àlex