cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vsslasd
Level: Powered On

MyTable[@fieldname] Field disambiguation

I am trying to utilize field disambiguation. All references state it should be: MyTable[@fieldname] But I am not having any luck. I have an error message in code. Can someone please show me how this works ? I have attached an image of the error. Thank you.

 

In addition, please note that the patch statement returns the Set Variable with the SQL Server Return RowId

12 REPLIES 12
PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: MyTable[@fieldname] Field disambiguation

Hi @vsslasd,

By chance, I posted a video about this exact topic today:

https://www.youtube.com/watch?v=ApdKlDTRprA

 

I notice that you are comparing GUID on both sides of the equation. That's problematic for a few reasons:

  • Do you have a GUID column in SQL? GUID is the name of a function in PowerApps which generates a random GUID.
  • Comparing GUID=GUID would return true for everything since PowerApps is not able to distinguish that you mean to compare GUID from two different tables.

 

My recommendation for you is that if the disambiguation operator is confusing to you, you should use RenameColumns instead to rename one of the columns virtually. 

Super User
Super User

Re: MyTable[@fieldname] Field disambiguation

Excellent advice @Mr-Dang-MSFT! Especially that about using RenameColumns to rename something virtually.

This blog post by Greg also contains some useful info on guids.

https://powerapps.microsoft.com/en-us/blog/fun-with-guids/

 

vsslasd
Level: Powered On

Re: MyTable[@fieldname] Field disambiguation

Thank you for your thoughts.

I have a column in a collection called "GUID" (it doesn't matter what it is called). Its source is the Powerapps function: GUID(), which automatically returns a unique identifier for each row, including new rows that are added to the collection.

 

I need a unique identifier in order to utilize the update statement in the ForAll.

 

Can you please provide me with an example by reviewing the images attached in my previous question ? I'm not certain how to utilize the rename column, or how to utilize the GUID =GUID (via a renamed column).

 

Thank you so very much. 

vsslasd
Level: Powered On

Re: MyTable[@fieldname] Field disambiguation

What I am trying to accomplish is something like the following:

If(connection.Connected,

ForAll(Filter(vRecRowId, IsBlank(RowId)),

Patch(Recommendation, First(Filter('[dbo].[Recommendation]',vRecRowId.[@GUID]=Recommendation.[@GUID]

{

RowId: RowId

}

)));

 

Please note from my previously posted image, that I am trying to retrieve the "Patched RowId" from the variable collection: vRecRowId. This collection, vRecRowId has the SQLServer RowId which was inserted into the SQL Server database and this collection also has the GUID in it. By associating the "GUID" column from the "vRecRowId" to the "Recommendation" collection, we can then update the RowId column in the Recommendation collection for those rows that have a blank RowId in the collection (even though they do have a RowID on SQL Server.

 

We need the RowId so that when we patch back to SQL Server, we have a binding reference in the Patch Update Statement.

 

Another approach would be to refresh the entire "Recommendation" collection AFTER the Patch statement, which would then fill in the RowId for all items that were patched, but that will put additional unnecessary stress on the server and take additional time to process, especially when we might only be needing to update the one column (RowId) of 16 rows instead refreshing a collection with all columns containing about 2000 rows.

 

I think the disambiguation may work, but I could be wrong. Do you understand what we are trying to do ? Is there a better way ?

 

One of our requirements is to be able to work offline, except when the actual patching occurs, and we need to patch only intermittently.

 

Thank you for your input and thoughts.

 

vsslasd
Level: Powered On

Re: MyTable[@fieldname] Field disambiguation

Please also note: the update to the RowId is used only as a reference in the Collection. The Patch to SQL Server never updates the RowId, since RowId is maintained on SQL Server.

 

We could patch the PowerApps GUID to SQL Server, and perhaps we will have to do this, but then there still is the issue of whether it exists or does not yet exist based upon whether it has been patched and saved to SQL Server and the RowId returned to the PowerApps collection, or if it has been patched to SQL Server and the RowId has not been returned to the PowerApps, but yet the record does exist in SQL Server.

 

A little confusing, perhaps.

vsslasd
Level: Powered On

Re: MyTable[@fieldname] Field disambiguation

Correction:

I think this is more like how the code should look:

 

If(connection.Connected,

ForAll(Filter(vRecRowId, Not IsBlank(RowId)),

Patch(Recommendation, First(Filter('[dbo].[Recommendation]',vRecRowId[@GUID]=Recommendation[@GUID] && IsBlank(Recommendation[@RowId]),

{

RowId: RowId

}

)));

Super User
Super User

Re: MyTable[@fieldname] Field disambiguation

Hi @vsslasd

Just to provide a bit more detail on @Mr-Dang-MSFT said earlier, this is how the RenameColumns method would look like. 

 

If(connection.Connected,
    ForAll(Filter(RenameColumns(vRecRowId, "GUID", "vRecRowId_GUID"), Not IsBlank(RowId)),
    Patch(Recommendation, 
First(Filter('[dbo].[Recommendation]',vRecRowId_GUID=GUID && IsBlank(RowId), {RowId: RowId} )));

This formula renames [vRecRowId].[GUID] to vRecRowId_GUID to prevent the need for disambiguation syntax in the Filter part of your Patch statement.

 

I've not tested this so I imagine there may be a small part of this that doesn't work. But hopefully, this explains the technique and you can use it to take you a closer to a working solution.

vsslasd
Level: Powered On

Re: MyTable[@fieldname] Field disambiguation

Thank you for your thoughts. I certainly don't mind using disambiguation, or renaming columns. Whatever is going to work. This is my code, and this isn't working. All I am trying to do is retrieve the RowID that was patched from SQL Server so that I have a Unique Identifier that we can refer to in the PowerApps environment.

 


If(Connection.Connected, 
Set(vRecommendationRowId,ForAll(Filter(Recommendation,ActiveStatus="New" || ActiveStatus="Changed"),
If(ActiveStatus="New",
        Patch('[dbo].[Recommendation]', Defaults('[dbo].[Recommendation]'),
        {
            Recommendation_ID: New_Recommendation_ID,
            Service_Call_ID: Service_Call_ID,
            LocationId: LocationId,
            CustomerId: CustomerId,
            EquipmentId: EquipmentId,
            Description: Description,
            DescriptionDetail: DescriptionDetail,
            UserId: UserId,
            Timestamp: Timestamp,
            IsDeleted: IsDeleted,
            RecordLongitude: RecordLongitude,
            RecordLatitude: RecordLatitude,
            PRM_Asset_Id: PRM_Asset_Id    
        }
        ),
        Patch('[dbo].[Recommendation]', First(Filter('[dbo].[Recommendation]', Recommendation_ID = New_Recommendation_ID)),
        {
            Description: Description,
            DescriptionDetail: DescriptionDetail,
            Timestamp: Timestamp,
            UserId: UserId,
            IsDeleted: IsDeleted,
            PRM_Asset_Id: PRM_Asset_Id
      }
)))));

ForAll(Filter(RenameColumns(vRecommendationRowId,  "GID", "vRecommendationRowId_GID"), IsBlank(RowId)),
    Patch(Recommendation, First(Filter(Recommendation,vRecommendationRowId_GID=GID),
          {RowId: RowId}
));

 

Any help is greatly appreciatred. 

 

Thank you in advance.

vsslasd
Level: Powered On

Re: MyTable[@fieldname] Field disambiguation

I think this will work: 

 


If(Connection.Connected, 
Set(vRecommendationRowId,ForAll(Filter(Recommendation,ActiveStatus="New" || ActiveStatus="Changed"),
If(ActiveStatus="New",
        Patch('[dbo].[Recommendation]', Defaults('[dbo].[Recommendation]'),
        {
            Recommendation_ID: New_Recommendation_ID,
            Service_Call_ID: Service_Call_ID,
            LocationId: LocationId,
            CustomerId: CustomerId,
            EquipmentId: EquipmentId,
            Description: Description,
            DescriptionDetail: DescriptionDetail,
            UserId: UserId,
            Timestamp: Timestamp,
            IsDeleted: IsDeleted,
            RecordLongitude: RecordLongitude,
            RecordLatitude: RecordLatitude,
            PRM_Asset_Id: PRM_Asset_Id    
        }
        ),
        Patch('[dbo].[Recommendation]', First(Filter('[dbo].[Recommendation]', Recommendation_ID = New_Recommendation_ID)),
        {
            Description: Description,
            DescriptionDetail: DescriptionDetail,
            Timestamp: Timestamp,
            UserId: UserId,
            IsDeleted: IsDeleted,
            PRM_Asset_Id: PRM_Asset_Id
      }
)))));

 

ForAll(Filter(RenameColumns(vRecommendationRowId,  "GID", "vGID"), IsBlank(RowId)),
    Patch(Recommendation, First(Filter(Recommendation,Text(GID)=vGID)),
          {RowId: RowId}
));