Is there a way to reference table rows when a using a combined key.

[FTong]

Newbee here... 

I posted a question a few days ago related to combining more than one column as a key in a table. 

Now it is clear that when two columns are selected as keys they form a completely new key using the values of the two columns selected. 

Follow up Question: 

is there a snipet of code to compare (or ref) a table to just one part of the combined key? 

Description: 

Table 1) Have an old jobs table that exists with about 10 years worth of jobs information (That do not have an ID column in it)  (Ref = Job_No - because they do not repeat)

Table 2) Have a time table that has test times and measurements recorded using the job number as reference  (Many measurements per job for different dates and times).  (Also has about 5 to 6 years worth  of jobs data.)

Table 3) Have a new jobs table that I am trying to use to bring in only the information for the jobs we are currently working on.  (Trying to Reduce amount of data to work with in the app - so it is easier to track as I work on the formulas)

1. This table has a unique ID column assigned to each record.  
2. This table will be used to calculate some rest results for comparison with other tests done at a later time. 
3. Need to bring in the information from table 1 and table 2. 

I am trying to link the information from Table 1 and Table 3 to Table 2.  But do not know how to ref the values to copy the data from tables 1 and table 2 into Table 3. 

Hope this makes sense... 

[TeeSee1]

T1 (one) - T2 (many) relation exists.

How do you want to store info in T3? The number of values you copy from T1 & T2 per T3 row varies, correct?

[FTong]

Yes, 

I like to select the Rate from a Table that holds values for several reaction rates for about 25 different materials.   so the process would be : 

1) select the material. 

2) select the reactionRate (RateA, RateB or RateC). 

3) enter quantity tested. 

4) read Reaction Rate from ref table. 

5) get calculations result. <---- this is yield ing zero every time. 

I though I have a value defined as text somewhere but I checked and all the columns that I am using are defined as decimals. 

(Any ideas/suggestions?)

[TeeSee1]

You confuse me again..

1) select the material.

  => you have a field to store this in T3, right?

2) select the reactionRate (RateA, RateB or RateC).

  => Where are these? part of the material table? A Separate table? You select one, two, three? different each time?

3) enter quantity tested. 

  => for each rate you select in 2 or one quantity for whatever rates you choose in 2)?

4) read Reaction Rate from ref table. 

  => how is this different from step 2)?

5) get calculations result. <---- this is yield ing zero every time. 

  => this is the issue from the other thread..

which part are you having problems with?

[Marc_Dillon]

Maybe:

https://help.appsheet.com/en/articles/2347639-contains

[Gianlucapozza]

Hello, this is exactly the same problem I'm trying to solve before trying a new approach:

Im building a simple CRM for field service and I have 1 mother table called "A" that should contain unique rows . In table "A" i have a column "UUID" that is key and I want to use it to ref table "B" containing multiple tasks for each row of table "A". Both table A and B grabs entrys from table "D" containing a list of locations that are readonly and one of these entry is a unique code for each location that is repeated in UUIDs for table A, B and table C.

Table C is made using another app and its the field report that operatives made in the field. This table has his key value but its not related to tables A and B. There is a ref to table D that im using to make labels and give additional infos. 

I would like to ref table B to A using table A UUID and also ref table C to A using the common non key field in both tables. 

Perfect solutions would be to use a computed key made of "UUID (of table A)"+"additionalcode (of table A)" and use CONTAINS() in REF_ROWS(). Also "additionacode" is already present inside table A "UUID" key values.

Is it possible to use CONTAINS() inside REF_ROWS() to ref using one of the values composing the computed key?

[FTong]

Gianlucapozza:

This approach did not work for me.
The overhead of splitting the key and doing searches based on the parts was
over my technical abilities in Appsheet.

Let me know if you find a simpler solution.

Drop me a line if you have questions, from your description it looks like
we are working on the same track.

[Marc_Dillon]

---

@Gianlucapozza wrote:

Is it possible to use CONTAINS() inside REF_ROWS() to ref using one of the values composing the computed key?

---

No

[Gianlucapozza]

Thanks! I was thinking that probably would be better to re-desing the field app to ref a task.