Broken REF

[jaichith]

Dear Friends,

I have 3 tables,

1. Pin code Directory

2. Customer Library

3. Consignments

table customer library and consignments fetches value from the pincode table as shown below

Since ref column type has certain restrictions, (it runs based on the key values not other values -Drop-down on a Ref column) so it has created broken references . ( Reason for using ref : I could get the list of consignments and customers (like following image) those who belong to each state, district. This helps me in data analysis)

@Tommy62 @AleksiAlkio @Steve @Suvrutt_Gurjar @Joseph_Seddik @Aurelien @SmartsheetGuru @MultiTech

Please help me sort out this issue

[jaichith]

Chat Gpt response

1. Virtual Columns: Instead of using direct references, you can create virtual columns in your tables that use expressions to fetch data from other tables. This can help you display the information you need for data analysis without relying solely on broken references.

2. Lookup Expressions: Use lookup expressions to retrieve data from other tables based on matching criteria. You can create virtual columns that use lookup expressions to fetch the necessary information from the Pin code Directory table.

Is it possible ?

[Marc_Dillon]

[Pincode] column in the first screenshot is the Ref to the pincode table, right?

[District] and [State] are just pulling values from the pincode table, from the selected record, right? They shouldn’t be Ref columns.
And you can use dereference expression instead of that ANY(SELECT())

https://help.appsheet.com/en/articles/1090811-dereference-expressions

[jaichith]

Thanks for suggestions. I am new to dereference. But I got some overview by your suggestions. I hope I can complete this.

Please have a look ~ What I expect in below images.

As you have [ District] and [state] should not be ref. Then how to get list of consignments and customers in Pincode Table (list - inline view)?

All this ref list should appear in the detailed view as we get in normal ref.

Red Colored " Empty " are broken refs . Corresponding consignment and customers should be listed here for understanding.

Does dereference solve this issue?

[Marc_Dillon]

I don’t quite understand what you’re wanting to do here. What exactly do you want to appear in the “related customer librarys by state”.

It looks like you maybe have a bit of a mess here from your fumbling around. I would first “reset” the situation by doing the following:

1. Delete all “Related…” VCs from the pincode table
2. Change all Ref columns in the 2 child tables to Text
3. Save the editor
4. Change the [Pincode] column in both child tables back to Ref
5. Save the editor again

Then you should have 2 auto-generated “Related…” VCs in pincode table, 1 for each child table.

[jaichith]

Thanks @Marc_Dillon for prompt reply. I don’t think above steps will solve my expectations since I know about already. ln simple words I will try convey requirements.

I want following requirements in Pincode table.

1. Related Customer by State

2. Related Consignment by State

3. Related Customer by District

4. Related Consignment by District.

[Marc_Dillon]

Those steps aren’t meant to solve your issue, they’re meant to reset your situation to get in more in line with a typical base setup, so as to avoid any oddities when trying to solve your issue.

I still don’t understand what you want. Separating related records by district and state makes no sense to me. Can you give some examples of your desired result?

[jaichith]

Dear @Marc_Dillon ,

As per your first reply. I have completed dereferences expression and changing the columns types to text type in child tables successfully.

Below image is the sample of desired result. which I was expecting.

Related Customer by District

This was completed by creating virtual column in table pincode tariff and select () was used to get list of Related Customer by District .

select(Customer Library[DISTRICT],[DISTRICT]=[_THISROW].[DISTRICT])

This select() function works and pulls the list of desired results but I have seen older post of @MultiTech , there he has said that not to use select() which slows down the app. If usage of select() function is not appropriate in my work, I request you @Marc_Dillon to give some other techniques to get desired result.

I hope my explanation was understandable and satisfactory to you.

Thank you.

[Marc_Dillon]

So your goal for the “…by district” is to show a list of all Customer records where the district matches the current Pincode record, no matter if the [pincode] itself matches?

[jaichith]

Yes, [District ] and [State]

[Marc_Dillon]

Then yah, use a SELECT (I’d use FILTER personally, but that’s just a wrapper for select on the key column). But [District] would not be an appropriate key column for the child table(s).

[jaichith]

Yes @Marc_Dillon , there will be several Districts and states so it can’t be key column.

What ever it is I should get list of related consignments and customers of district and state as mentioned earlier to solve my case.

Could you please provide me the expression by using filter () function? Let me try it.

[MultiTech]

Perhaps another method might be to include additional support tables that you can include references too, which can then be used as a cross-reference when refining the query?

• What I mean is, what if you had a ‘Districts’ table, and what if your records were ref connected to those as well as whatever table you’ve been working with so far.
• If these records were connected to the District table, that would give you a [Related ChildRecords] on the District… which would contain only the records that match whatever record you’re dealing with.

Though INTERSECT() you can find common elements in two lists.

• So… if the the columns that a user is using to make these criteria selections are references… you could pull the list of [Related ChildRecords] from the District… and AND from, say, the State (which would also need a similar ref connection setup like the Distrcit)… you could intersect those two together, giving you a more refined list.

INTERSECT(
  [District_Ref_Column].[Related ChildRecords], 
  [State_Ref_Column].[Related ChildRecords]
)

The resulting list would be the common elements between those two lists.

• So if there were 20 records for the district you selected
• and 30 records for the state
• But only 5 where the district and state match the selection by the user
  • Only those 5 records would show
  • And this is all done using readily available data, without having to re-query your data.

[jaichith]

@MultiTech , thanks for the support.

I had the same idea which you given in 1st part of the reply (creating separate table for District) . Already I have nearly 20 Tables , adding some more tables won’t be burden to the app?

Is Adding an extra tables for District and State better idea than using select () function ? In -case if yes, I will adapt adding new tables and make them as Ref in child table. Please confirm because you have specified in your appster to avoid select() function.

2nd part- intersect()

Intersect () function has made me to astonished and it was quite interesting but as far as now I don’t have a need to get common list between 2 list.

Part 1 made effective if you confirm.

[MultiTech]

@jaichith

Please confirm because you have specified in your appster to avoid select() function.

Appster is the codename for an Ai project I’ve been working on these past few months. (learn more here)

• I have worked very hard to get it to avoid SELECT() when assisting people, yes. It will still use SELCT() if it can’t find a more efficient solution - which usually happens due to an insufficient amount of info - but I’ve been working with it to really steer it away from these build methods.
• If you can provide Appster with sufficient info about your data schema, and describe the nuance of what you’re wanting to accomplish, Appster seems to regularly answer these questions more than sufficiently. In fact some of the answers it provides are quite clever, surprising me sometimes with the approach it takes.
• --------------------------------------------------------------------------------------------------------

@jaichith

Is Adding an extra tables for District and State better idea than using select () function ?

Yes. % of the time.

[jaichith]

@MultiTech ok I will add separate tables for districts and state and connect to child tables.

@MultiTech & @Marc_Dillon thanks for your valuable contribution of time and knowledge.