What's the trick to Looking up a record in an Oracle database where a date column is part of a primary key?

(0) Share

Report

Posted on by djuggler

I have my items to my gallery1 set to: 'Tablename'

I have my items to my gallery2 set to:

LookUp(

'Tablename',

SomeNumber = Gallery2.Selected.SomeNumber &&

SomePhaseID = Gallery2.Selected.SomePhaseID &&

BEGINDATE = Gallery2.Selected.BEGINDATE &&

STOPDATE = Gallery2.Selected.STOPDATE

)

Should be whatever I select in gallery1 shows in gallery2.

If I change gallery2 items to:

LookUp(

'Tablename',

SomeNumber = Gallery2.Selected.SomeNumber &&

SomePhaseID = Gallery2.Selected.SomePhaseID

)

then it works except that it returns too many records. The moment I introduce the dates, no data comes back. I've tried wrapping the dates in DateTimeValue() and Date() but I get no love.

This is an Oracle database. What am I doing wrong?

Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

@djuggler

Lookup() returns a single record that matches its criteria. Thus, it is inappropriate for the Items property of a gallery since only a single record is shown. Instead, Filter() will return a table, even if it is only one record.

The typical scenario for two galleries is when you want to display a one-to-many (parent to child) relationship between two tables that are related. If I am interpreting your problem, it is that you are trying to demonstrate a one-to-many relationship.

If the first galleryItems property is Table1, the parent table, Then the second gallery's items are a table that contains the child records for a parent table. The second table will have a field that is the primary key of the parent table, say parentID. In that case the Items property of Gallery2 will be Filter(childtable, parentID = Gallery1.Selected. ID)

Was this reply helpful? Yes No
djuggler 35 on at

Like (0)

Report

Great answer. To be honest, I set up this example as a means of troubleshooting. What I actually have is a gallery that when a row is selected, it populates an editform. I have a save button that onSelect runs this (updated per your recommendation). Using the code below, when I change a value and click save, I no longer get an error but patch also does not seem to update the existing row nor add a new row. If I put this lookup into gallery2 and select a row from gallery1 nothing shows up in gallery2 so I suspect I am still not matching the selected record to the record in the database and I think that is going to come down to the Oracle date format. Currently Gallery1.Selected.BEGINDATE returns "11/15/2022 6:00 PM" Removing the dates from the query makes data show up but the dates are part of the primary key. It's a combined key between SomeNumber, SomePhaseID, BEGINDATE, and ENDDATE. If I had an id column in the database this would be a non-issue.

Patch(
'OracleDatabase',
First(
Filter(
'OracleDatabase',
SomeNumber = Gallery1.Selected.SomeNumber &&
SomePhaseID =Gallery1.Selected.SomePhaseID &&
BEGINDATE =Gallery1.Selected.BEGINDATE &&
STOPDATE =Gallery1.Selected.STOPDATE
)
)
,
{
SomeNumber: SomeNumber_DataCard1.Update,
PENALTY_FEE_AMT: PENALTY_FEE_AMT_DataCard2.Update,
SomePhaseID: SomePhaseID_DataCard4.Update,
BEGINDATE: BEGINDATE_DataCard1.Update,
STOPDATE: STOPDATE_DataCard2.Update,
COMMENTS: COMMENTS_DataCard2.Update
}
);
If(
// check if there were any errors when the test score was submitted
!IsEmpty(Errors('OracleDatabase')),
// if true, show any error message
Notify(
Concat(Errors('OracleDatabase'), Column&": "&Message),
NotificationType.Error
),
// else, go to success screen
Refresh('OracleDatabase');
);

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

@djuggler

In my MS Access days, I used to use compound primary keys but I found that they are impossible to deal with when doing bulk operations, particularly when I wanted to migrate my tables to Dataverse. I have caved in and only use the ID metadata fields like in SharePoint or I create a sequential numeric ID field in Dataverse since the primary key assignments in Dataverse are GUID.

Was this reply helpful? Yes No
djuggler 35 on at

Like (0)

Report

Unfortunately it is not my design. I've reached out to the dba to request an id column.

Was this reply helpful? Yes No